SQL AUTO INCREMENT Field

The Auto Increment is a keyword used for auto-incrementing a value of a field in the table.

The Auto Increment field can only be used with the Integer field.

The primary key of a table is often indicated to be an auto-incremented field. So the primary key will be generated every time a new record is inserted.

Syntax for MySQL, MariaDB

MySQL and MariaDB use the AUTO_INCREMENT keyword to define a column as an auto-increment.

The following SQL statement will define the "emp_id" column to be an auto-increment primary key field of the "Employee" table:

CREATE TABLE employee (
  emp_id INT NOT NULL AUTO_INCREMENT,
  first_name VARCHAR(45),
  last_name VARCHAR(45),
  salary DOUBLE(12, 2),

  PRIMARY KEY(emp_id)
);

By default, the AUTO_INCREMENT sequence will start with the value 1, and it will increment by 1 for each new record.

If you want, you can make the AUTO_INCREMENT sequence start with another value. The following SQL statement will make the AUTO_INCREMENT of the "Employee" table start with the value 50.

ALTER TABLE employee AUTO_INCREMENT = 50;

We can now insert a new record in the "Employee" table, and we do not need to specify a value for the "emp_id" column because a unique value from the AUTO_INCREMENT sequence will be added automatically for the "emp_id" column.

INSERT INTO employee(first_name, last_name, salary)
                       VALUES('Mary', 'Martinez', 8000);

Syntax for Oracle

The syntax to create an auto-increment field in Oracle is a little bit different.

The first step to create an auto-increment is to define a sequence object which will have the purpose to generate a number sequence.

The following SQL statement will create a sequence object called "seq_employee", which will start with 1 and increment by 1. It will also use a cache up to 20 values for performance.

CREATE SEQUENCE seq_employee
MINVALUE 1
START WITH 1 
INCREMENT BY 1
CACHE 20;

After creating the sequence, the next step will be to use this sequence to insert a new record in the "Employee" table. We will have to use the nextval function to fetch the next value from "seq_employee".

The following SQL statement will insert a new record into the "Employee" table. The "emp_id" column will be filled by the next number from "seq_employee":

INSERT INTO employee(first_name, last_name, salary)
                       VALUES(seq_employee.nextval, 'Mary', 'Martinez', 8000);

Syntax for SQL Server

SQL Server uses the IDENTITY keyword to define a column as an auto-increment.

The following SQL statement will define the "emp_id" column as an auto-increment primary key field of the "Employee" table. It will determine the value 1 as the starting for the IDENTITY, and it will be incremented by 1 for each new record.

CREATE TABLE employee (
  emp_id INT IDENTITY(1, 1) PRIMARY KEY,
  first_name VARCHAR(45),
  last_name VARCHAR(45),
  salary DOUBLE(12, 2)
);

If you want to specify that the "emp_id" column starts at value 20 and increment by 2, you can change the IDENTITY to IDENTITY(20, 2).

To insert a new record in the "Employee" table, we will not need to specify a value for the "emp_id" column (a unique auto-generated value will be added automatically).

The following SQL statement will insert a new record into the "Employee" table. The "emp_id" column would be filled with an auto-generated unique value.

INSERT INTO employee(first_name, last_name, salary)
                       VALUES('Mary', 'Martinez', 8000);