Create tables with an autonumbering primary key in SQL
Primary key fields can not contain a NULL value and each value must be unique. That is really what a primary key field is: a combination of a NOT NULL and a UNIQUE constraint. It can be tedious to come up with new primary key values every time you (or your application) enter a new record. That is why the job of generating a unique primary key value for each new record can be left to the database.
The database can generate an automatically incrementing integer value for each new record. So records would automatically get incrementing primary key values like 0,1,2,3,4,5... etc.
Creating an autonumbering primary key field in Mysql
In Mysql, creating an autonumbering primary key field is done as follows.
CREATE TABLE Customer
customer_id INTEGER NOT NULL AUTO_INCREMENT,
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL,
This code would create a table for which the database would generate the primary key.
Sample data for a table with an auto_increment primarykey. The values in the customer_id field ar
e generated by thedatabase, in this case Mysql.
Creating an autonumbering primary key field in Postgressql
In Postgresql a SEQUENCE is used to create an autonumbering primary key field. A sequence is a definition of the numbers that Postgressql can choose from when it picks a new primary key value.
In Postgresql, creating a table with an autonumbering primary key field is therefore done in two steps. First you create the sequence, then the actual table.
my_primary_key_sequence START 1;
CREATE TABLE beers (
beer_id INTEGER PRIMARY KEY DEFAULT nextval('my_primary_key_sequence'),
name varchar(40) NOT NULL,
country_code char(2) NOT NULL
Note that the DEFAULT value of the beer_id field is a value taken from the my_primary_key_sequence SEQUENCE created earlier.
Creating an autonumbering primary key field in Oracle
Creating an autonumbering primary key field in Oracle can be done in multiple ways. I will show you an easy approach that resembles the approach I showed you for Postgresql. It is a three step proces.
- Create the sequence
- Create the table
- Create a trigger
CREATE SEQUENCE my_test_sequence
start with 1 increment by 1;
CREATE TABLE cars (
car_id integer not null,
name varchar(40) not null,
weight integer not null,
constraint pk_cars primary key(car_id)
CREATE TRIGGER new_cars_record
before insert on cars
for each row
when(new.car_id is null)
select my_test_sequence.nextval into :new.car_id from dual;
The CREATE SEQUENCE statement creates a sequence called my_test_sequence that starts with one and increments with one for each new selection from the sequence.
The CREATE TABLE statement obviously creates the table.
The CREATE TRIGGER statement creates a trigger. A trigger is an action that is fired in response to a certain event. In this case the event is "before insert on cars", or, in other words, this action fires right before a new record is inserted into the cars table. When the car_id is null (that is, when the new car record has an empty value for the primary key), the database selects a new value from the sequence my_test_sequence and uses it as a value for the car_id.
Creating an autonumbering primary key field in Sql Server
Creating an autonumbering field in SQL Server is done using the IDENTITY keyword. This simple create script will create a dogs table with an autonumbering primary key field called dog_id.
CREATE TABLE dogs (
dog_id INTEGER IDENTITY(1,1) NOT NULL,
name varchar(100) NOT NULL,
color varchar(20) NOT NULL,
PRIMARY KEY (dog_id)
The IDENTITY(1,1) part of the query tells SQL Server this is a field that must start at number 1 and be incremented with 1 for each new record.
As you can see, different databases use different syntax for creating autonumbering fields. On the next page I will show you how foreign keys are created, another important part of creating tables with SQL.