We can define columns in databases which will auto increment its values whenever we add a new row. Different databases have different ways to do so and even call them differently like auto increment, identity column etc. We will discuss the auto increment for some databases here. Please feel free to add other database details that you know through the comments.

Auto Increment in MySQL

 The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows:

CREATE TABLE empAutoInc(

id INT PRIMARY KEY AUTO_INCREMENT,

name varchar(20));

There can be only one auto column and it must be defined as a key. You will get an error otherwise; even you can't use NOT NULL.

You can test this by inserting few rows without providing value for the auto increment column, and then doing a select:

insert into empAutoInc(name) values('abc');

insert into empAutoInc(name) values('def');

select * from empAutoInc;

Output will be:

1    abc
2    def

I have tested this with MySQL 5.0.45.

Auto Increment (IDENTITY) in MSSQL

The IDENTITY attribute can be used to generate a unique identity for new rows:

create table AutoEmp(

id INT IDENTITY,

name varchar(20));

Unlike MySQL auto increment (identity) field doesn't have to be defined as primary key.

You can test this by inserting few rows without providing value for the auto increment column, and then doing a select:

insert into AutoEmp values ('hjk');

insert into AutoEmp values ('asd');

select * from AutoEmp;

Unlike MySQL, you don't have to specify columns after table name and still pass only values for non-identity fields.

Output will be:

1    hjk
2    asd

I have tested this with MS SQL 2012.

Auto Increment in Oracle

There is no identity column in Oracle, but we can use sequence to get a similar effect.

First, create the table

create table AutoEmp (

id number(10),

name varchar2(25),

constraint pk_id PRIMARY KEY(id)

);

Then, create the sequence:

Create sequence emp_seq

start with 1

increment by 1

minvalue 1

maxvalue 100;

Now, use the sequence to insert data and then do a select to verify:

insert into AutoEmp (id,name) values(emp_seq.nextval,'HJK');

insert into AutoEmp (id,name) values(emp_seq.nextval,'HJK');

select * from AutoEmp;

I have not tested it yet myself.

Auto Increment in PostgreSQL

There is no identity column, but we can use sequence to get a similar effect as in Oracle.

Auto Increment in Sybase

The IDENTITY attribute can be used to generate a unique identity for new rows simular to MSSQL.

I have not tested it yet myself.

Auto Increment in Informix

You can use serial columns in Informix for the auto increment column.

create table AutoEmp (

id serial,

name varchar(20),

);

An automatic new value will only be created when if we insert a new entry without specifying the serial field, or when we insert a value "0" to serial field.

I have not tested it yet myself.

Auto Increment in DB2

We can use 'GENERATED ALWAYS AS IDENTITY' to generate a unique identity for new rows:

create table AutoEmp (

id INT NOT NULL GENERATED ALWAYS AS IDENTITY,

name varchar(20),

);

I have not tested it yet myself.

Auto Increment in Terradata

We can use 'GENERATED ALWAYS AS IDENTITY' to generate a unique identity for new rows similar to DB@:

create table AutoEmp (

id NUMERIC(15, 0) NOT NULL GENERATED ALWAYS AS IDENTITY,

name varchar(20),

);