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 #

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

Try it

Second, insert a new row into the ranks table:

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

Try it

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)

Try it

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)

Try it

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.

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

Try it

Second, insert a row into the ranks table:

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

Try it

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)

Try it

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

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

Try it

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)

Try it

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

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

Try it

Second, insert another row into the ranks table:

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

Try it

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)

Try it

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