SQL IDENTITY

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 ) ]

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
);

Second, insert a new row into the ranks table:

INSERT INTO ranks (rank_name)
VALUES
    ('A');

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;

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');

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.

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
);

Second, insert a row into the ranks table:

INSERT INTO ranks (rank_name)
VALUES
    ('A');

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');

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;

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
); 

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');

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

SELECT
    *
FROM
    ranks;

Second, insert another row into the ranks table:

INSERT INTO ranks (rank_name)
VALUES
    ('B');

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

SELECT
    *
FROM
    ranks;