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)
