Contents

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,
email VARCHAR(50),
birthdate DATE,
average_monthly_visits INTEGER,
PRIMARY KEY(customer_id)
)

This code would create a table for which the database would generate the primary key.

Create a table with a auto incrementing primary keySample 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.

CREATE SEQUENCE 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.

  1. Create the sequence
  2. Create the table
  3. 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)
begin
    select my_test_sequence.nextval into :new.car_id from dual;
end;

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.