SQL Identity

Summary: in this tutorial, you will learn how to use the GENERATED AS IDENTITY to create the SQL identity column for a table.

Introduction to SQL identity column

SQL identity column is a column whose values are automatically generated when you add a new row to the table. To define an identity column, you use the GENERATED AS IDENTITY property as follows:

column_name data_type GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY[ ( sequence_option ) ]
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• The data_type can be any integer data type.
• The GENERATED ALWAYS generates sequential integers for the identity column. If you attempt to insert (or update) a value into the GENERATED ALWAYS AS IDENTITY column, the database system will raise an error.
• The GENERATED BY DEFAULT generates sequential integers for the identity column. However, if you provide a value for insert or update, the database system will use that value for insert instead of using the auto-generated value.

SQL identity column examples

A) GENERATED ALWAYS example

First, creates a table named ranks which has the rank_id column as the identity column:

CREATE TABLE ranks (
    rank_id INT GENERATED ALWAYS AS IDENTITY,
    rank_name CHAR
);
Code language: SQL (Structured Query Language) (sql)

Second, insert a new row into the ranks table:

INSERT INTO ranks (rank_name)
VALUES
    ('A');
Code language: SQL (Structured Query Language) (sql)

Because rank_id column has the GENERATED AS IDENTITY property, the database system generates a sequential integer for it as shown in the query result below:

SELECT
    *
FROM
    ranks;
Code language: SQL (Structured Query Language) (sql)

Third, insert a new row by providing values for both rank_id and rank_name columns:

INSERT INTO ranks (rank_id, rank_name)
VALUES
    (2, 'B');
Code language: SQL (Structured Query Language) (sql)

The database system issued the following error:

[Err] ERROR:  cannot insert into column "rank_id"
DETAIL:  Column "rank_id" is an identity column defined as GENERATED ALWAYS.
Code language: SQL (Structured Query Language) (sql)

To fix the error, you use the GENERATED BY DEFAULT AS IDENTITY.

B) GENERATED BY DEFAULT AS IDENTITY example

First, drop the ranks table and recreate it. We use the GENERATED BY DEFAULT AS IDENTITY property this time:

DROP TABLE ranks;

CREATE TABLE ranks (
    rank_id INT GENERATED BY DEFAULT AS IDENTITY,
    rank_name CHAR NOT NULL
);
Code language: SQL (Structured Query Language) (sql)

Second, insert a row into the ranks table:

INSERT INTO ranks (rank_name)
VALUES
    ('A');
Code language: SQL (Structured Query Language) (sql)

It works as expected.

Third, insert another row with a value for the rank_id column:

INSERT INTO ranks (rank_id, rank_name)
VALUES
    (2, 'B');
Code language: SQL (Structured Query Language) (sql)

Note that unlike the previous example that uses the GENERATED ALWAYS AS IDENTITY, this statement also works.

C) Sequence options example

You can specify the starting and increment values for the identity column with the following syntax:

START WITH starting_value 
INCREMENT BY increment_value;
Code language: SQL (Structured Query Language) (sql)

See the following example:

DROP TABLE ranks;

CREATE TABLE ranks (
    rank_id INT GENERATED BY DEFAULT AS IDENTITY 
    (START WITH 10 INCREMENT BY 10),
    rank_name CHAR NOT NULL
); 
Code language: SQL (Structured Query Language) (sql)

In this example, the auto-generated value for the rank_id column starts with 10 and the increment value is also 10.

First, insert a new row into the ranks table:

INSERT INTO ranks (rank_name)
VALUES
    ('A');
Code language: SQL (Structured Query Language) (sql)

The starting value for rank_id column is ten as shown below:

SELECT
    *
FROM
    ranks;
Code language: SQL (Structured Query Language) (sql)

Second, insert another row into the ranks table:

INSERT INTO ranks (rank_name)
VALUES
    ('B');
Code language: SQL (Structured Query Language) (sql)

The value for the rank_id of the second row is 20 because of the increment value option.

SELECT
    *
FROM
    ranks;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to define the SQL identity column using the GENERATED AS IDENTITY.