SQL Auto Increment

Summary: in this tutorial, you will learn how to use the SQL auto increment to define a column whose values are sequential integers which are automatically generated.

When designing a table, we often use the surrogate primary key whose values are sequential integers generated automatically by the database system.

This primary key column is known as an identity or auto increment column.

When a new row is inserted into the auto-increment column, an auto-generated sequential integer is used for the insert.

For example, if the value of the first row is 1, then the value of the second row is 2, and so on.

Some databases systems allow you to define the starting and increment values. For example, when the starting value is 10 and the increment value is 20, the sequential integers are 10, 20, 30 and so on.

Different database systems implement the auto increment column differently. Let’s see how the SQL auto increment column is implemented in some major database systems.

SQL auto increment column in MySQL

MySQL uses AUTO_INCREMENT property to define an auto-increment column. See the following example:

CREATE TABLE leave_requests (
    request_id INT AUTO_INCREMENT,
    employee_id INT NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    leave_type INT NOT NULL,
    PRIMARY KEY(request_id)
);
Code language: SQL (Structured Query Language) (sql)

In this example, we created a new table named leave_requests with the request_id is the auto increment column.

SQL auto increment column in Oracle

Oracle uses the identity column for creating an auto increment column as follows:

CREATE TABLE leave_requests (
    request_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    employee_id INT NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    leave_type INT NOT NULL,
    PRIMARY KEY(request_id)
);
Code language: SQL (Structured Query Language) (sql)

SQL auto increment column in PostgreSQL

Similar to Oracle, PostgreSQL also uses the identity column for defining auto increment columns:

CREATE TABLE leave_requests (
    request_id INT GENERATED BY DEFAULT AS IDENTITY,
    employee_id INT NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    leave_type INT NOT NULL,
    PRIMARY KEY(request_id)
);
Code language: SQL (Structured Query Language) (sql)

SQL auto increment column in SQL Server

SQL Server uses IDENTITY property to define an auto increment column as shown in the following query:

CREATE TABLE leave_requests (
    request_id INT IDENTITY(1,1),
    employee_id INT NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    leave_type INT NOT NULL,
    PRIMARY KEY(request_id)
);
Code language: SQL (Structured Query Language) (sql)

SQL auto increment column in DB2

Like Oracle, DB2 uses the identity column for defining the auto-increment column:

CREATE TABLE leave_requests (
    request_id INT GENERATED BY DEFAULT AS IDENTITY,
    employee_id INT NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    leave_type INT NOT NULL,
    PRIMARY KEY(request_id)
);
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the SQL auto increment column to define a column whose values are automatically generated sequential numbers.