SQL AUTO INCREMENT Field

AUTO INCREMENT fields are used for auto generating values for particular column whenever new row is being inserted.

Very often the primary key of a table needs to be created automatically; we define that field as AUTO INCREMENT field.

Syntax for the AUTO INCREMENT field is:

column_name data_type constraint AUTOINCREMENT;
column_name - is the name of the column (usually primary key)
data_type - is the type of data column is storing
constraint – is the constraint definition (if any)

The AUTOINCREMENT keyword is termed by other keywords in other database systems

MySQL: AUTO_INCREMENT

column_name data_type constraint AUTO_INCREMENT;

MySQL: AUTO_INCREMENT Example

CREATE TABLE Employee
(
EmployeeID INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(100) NOT NULL,
.
.
)

Default starting value of AUTO_INCREMENT is 0

To modify it alter value as in below example.

ALTER TABLE Employee AUTO_INCREMENT=10000

SQL SERVER: PRIMARY KEY IDENTITY

column_name data_type constraint IDENTITY;

SQL SERVER: Example

CREATE TABLE Employee
(
EmployeeID INT PRIMARY KEY IDENTITY,
Name VARCHAR(100) NOT NULL,
.
.
)

The default starting value of IDENTITY is 1 and will increment by 1 for each record. To modify it alter value as in below example.

EmployeeID INT PRIMARY KEY IDENTITY(1000,2)

SQL SERVER: ORACLE: Sequence

Oracle uses different approach for generating unique identifiers. An auto incremented filed with SEQUENCE object is created first and this value is assigned to table’s column

CREATE SEQUENCE sequence_name
START WITH start_value
INCREMENT BY increment_value

INSERT INTO table_name(autoincrement_column, column1, column2, ..)
VALUES(sequence_name.nextval, value1, value2, ..)
sequence_name - is the sequence to be created
start_value – is the start value of identifier
increment_value – is the increment value to which previous value should be incremented
table_name – is the table name on which auto increment value is needed
autoincrement_column– is the column whose value to be auto generated

SQL SERVER: ORACLE Example

CREATE SEQUENCE sequence_employee
START WITH 1
INCREMENT BY 1

INSERT INTO Employee(EmployeeID, Name, ..)
VALUES(sequence_employee.nextval, “Test”, ..)

MS ACCESS: AUTO INCREMENT

column_name data_type constraint AUTOINCREMENT;

Example: MS AUTO INCREMENT

CREATE TABLE Employee
(
EmployeeID INT PRIMARY KEY AUTOINCREMENT,
Name VARCHAR(100) NOT NULL,
.
.
)

The default starting value of AUTOINCREMENT is 1 and will increment by 1 for each record. To modify it alter the value as in example below.

EmployeeID INT PRIMARY KEY AUTOINCREMENT(1000,2)