SQL Auto Increment

Auto Increment of a column value in the table

  • Sometimes we want to generate a unique key for a column in a table.
  • Almost all RDBMS provides the ability to define a column of integer type that automatically creates new value for new rows in the table.
  • AUTO_INCREMENT column must be indexed. This means that the column must be included as part of a PRIMARY KEY, UNIQUE index or CONSTRAINT definition
  • Auto increment specified slightly differently as regards the individual RDBMS.

MySQL

In MySQL RDBMS you can enter AUTO_INCREMENT at the end of selected column.

MySQL Example:
CREATE TABLE customer (
  CUSTOMER_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  FIRST_NAME VARCHAR(30) NULL,
  LAST_NAME VARCHAR(40) NULL,
  EMAIL VARCHAR(30) NULL
 )
or :
CREATE TABLE customer (
  CUSTOMER_ID INT NOT NULL AUTO_INCREMENT,
  FIRST_NAME VARCHAR(30) NULL,
  LAST_NAME VARCHAR(40) NULL,
  EMAIL VARCHAR(30) NULL,
  PRIMARY KEY (CUSTOMER_ID)
 )
or :
CREATE TABLE customer (
  CUSTOMER_ID INT NOT NULL AUTO_INCREMENT,
  FIRST_NAME VARCHAR(30) NULL,
  LAST_NAME VARCHAR(40) NULL,
  EMAIL VARCHAR(30) NULL,
  CONSTRAINT pk_CUSTOMER PRIMARY KEY (CUSTOMER_ID)
 )

The starting value for AUTO_INCREMENT is default 1, and it will increment by 1 for each new row.

SQL Server

In SQL Server RDMS you must enter IDENTITY keyword as part of the specification of the selected column.

Example:
CREATE TABLE customer (
  CUSTOMER_ID INT IDENTITY(1,1) PRIMARY KEY,
  FIRST_NAME VARCHAR(30) NULL,
  LAST_NAME VARCHAR(40) NULL,
  EMAIL VARCHAR(30) NULL
 )

In this example the starting value for IDENTITY is 1, and it will increment by 1 for each new row. A specification of IDENTITY(10,10) gives a start value of 10, and will increment by 10 for each new row.

PostgreSQL

In PostgreSQL RDMS you can enter SERIAL as type of the selected column.

Example:
CREATE TABLE customer (
  CUSTOMER_ID SERIAL PRIMARY KEY,
  FIRST_NAME VARCHAR(30) NULL,
  LAST_NAME VARCHAR(40) NULL,
  EMAIL VARCHAR(30) NULL
 )

The SERIAL column data will then increase by 1 for each row you insert into the table.

Oracle

In Oracle RDMS you must do it in a two step order.

  1. You must create an autonumber field by creating a SEQUENCE. A SEQUENCE is an object in Oracle that is used to generate a number sequence.
    ORACLE SEQUENCE example:
    CREATE SEQUENCE customer_seq
      MINVALUE 1
      START WITH 1
      INCREMENT BY 2
      CACHE 20;

    This will creates a sequence object called customer_seq, that starts with 1 and will increment by 1. It will also cache up to 20 values for performance.

  2. The next step is when you insert row into the table and use customer_seq.nextval to get new values from the sequence.
    ORACLE insert example:
    INSERT INTO customer (CUSTOMER_ID,FIRST_NAME,LAST_NAME)
    VALUES (customer_seq.nextval,'Ricard','Moon')
© 2010 by Finnesand Data. All rights reserved.
This site aims to provide FREE programming training and technics.
Finnesand Data as site owner gives no warranty for the correctness in the pages or source codes.
The risk of using this web-site pages or any program codes from this website is entirely at the individual user.