SQL Auto Increment

The SQL AUTO INCREMENT keyword allows a unique number to be generated automatically whenever a new record is inserted into a table.

SQL AUTO INCREMENT Field

This is often the primary key field that we would like to create automatically every time a new record is inserted.

Syntax for MySQL

The following SQL statement defines the “customerID” column to be an auto-increment primary key field in the “customer” table:

CREATE TABLE customer (
  customerID int NOT NULL AUTO_INCREMENT,
  companyName varchar(255) NOT NULL,
  name varchar(255),
  companyAge int,
  PRIMARY KEY (customerID)
);

How can we define an initial value for increment in MySQL?
To let the AUTO_INCREMENT sequence start with another value, we can use the following SQL statement:

 ALTER TABLE customers AUTO_INCREMENT = 100;

So once the auto-increment is set for the column we don’t have to specify a value for the “customerID” column.

 
  INSERT INTO customers (companyName, name, companyAge)
  VALUES ('Thomas','Monsen', 12);

Syntax for SQL Server

The following SQL Server statement defines the “customerID” column to be an auto-increment primary key field in the “customer” table:

  CREATE TABLE customer (
    customerID int IDENTITY(1,1) PRIMARY KEY,
    companyName varchar(255) NOT NULL,
    name varchar(255),
    companyAge int
  );

How can we define an initial value for increment in MS SQL Server?
To specify that the “customerID” column should start at value 10 and increment by 5, change it to IDENTITY(10,5).
So on insert of first record it will set “customerID” to 10
and on insert of second record it will set “customerID” to 15
So once the auto-increment is set for the column we don’t have to specify a value for the “customerID” column.

 
  INSERT INTO customers (companyName, name, companyAge)
  VALUES ('Thomas','Monsen', 12);

Syntax for MS Access

The following SQL statement for MS Access defines the “customerID” column to be an auto-increment primary key field in the “customer” table:

  CREATE TABLE customer (
    customerID AUTOINCREMENT PRIMARY KEY,
    companyName varchar(255) NOT NULL,
    name varchar(255),
    companyAge int
  );

How can we define an initial value for increment in MS Access?
To specify that the “customerID” column should start at value 10 and increment by 5, change it to AUTOINCREMENT(10,5).
So on insert of first record it will set “customerID” to 10
and on insert of second record it will set “customerID” to 15

So once the auto-increment is set for the column we don’t have to specify a value for the “customerID” column.

 
  INSERT INTO customers (companyName, name, companyAge)
  VALUES ('Thomas','Monsen', 12);

Syntax for Oracle

In Oracle, the code is a little bit more tricky and different.
In Oracle, you will have to create an auto-increment field with the sequence object (this object generates a number sequence).

How can we define an initial value for increment in Oracle?
Use the CREATE SEQUENCE syntax:

  CREATE SEQUENCE seq_customer
  MINVALUE 1
  START WITH 1
  INCREMENT BY 1
  CACHE 10;

The above code creates a sequence object called seq_customer, which starts with 1 and will increment by 1.
It will also cache up to 10 values for performance. The cache option specifies how many sequence values will be stored in memory for faster access.

To insert a new record into the “customer” table, we will have to use the nextval function.

  INSERT INTO customers (customerID, companyName, name, companyAge)
  VALUES (seq_customer.nextval, 'Thomas','Monsen', 12);

.
SQL Complete Guide