SQL Identity Column

Summary: in this tutorial, you’ll 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 that automatically generates unique integer for each row when you insert a new row to the table.

To define an identity column, you use the IDENTITY property with the following syntax:

column_name datatype GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [sequence_option]

In this syntax:

column_name: The name of the identity column.
datatype : The datatype of the identity column which can be any integer data type.
GENERATED ALWAYS generates sequential integers for the identity column and prevents inserting a value into the column. If you attempt to insert a value into the GENERATED ALWAYScolumn, the database system will raise an error.
GENERATED BY DEFAULT works like the GENERATEd ALWAYS but allows you to insert a value into the column to override the generated value.
sequence_option: Controls how the identity generate values.

Here are the options of the sequence_option:

START WITH: Defines the starting number of the sequence.
INCREMENT BY: Sets the increment number for the sequence.
MINVALUE: Specifies the minimum number for the sequence.
MAXVALUE: Specifies the maximum number for the sequence.
CACHE: Defines the number of sequence numbers that the database system will cache for performance.
NOCACHE: Turns off caching of sequence numbers.
CYCLE: Restarts the sequence number when it reaches the maximum value.
NOCYCLE: Prevents the sequence from restarting.

In practice, you often use an identity column as the primary key column of a table, where each integer uniquely identifies each row in the table.

SQL Identity column examples #

Let’s explore some examples of using SQL identity columns.

GENERATED ALWAYS AS IDENTITY example #

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

CREATE TABLE ranks (
  rank_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  rank_name CHAR NOT NULL
);

Try it

Second, insert a new row into the ranks table:

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

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;

Try it

 rank_id | rank_name
---------+-----------
       1 | A

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

Try it

The database system issued the following error:

ERROR:  cannot insert a non-DEFAULT value into column "rank_id"

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

GENERATED BY DEFAULT AS IDENTITY example #

First, drop the ranks table:

DROP TABLE ranks;

Try it

Second, recreate the ranks table with the GENERATED BY DEFAULT AS IDENTITY property:

CREATE TABLE ranks (
  rank_id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  rank_name CHAR NOT NULL
);

Try it

Third, insert a row into the ranks table:

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

Try it

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

INSERT INTO
  ranks (rank_id, rank_name)
VALUES
  (2, 'B');

Try it

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

Finally, retrieve data from the ranks table:

SELECT
  rank_id,
  rank_name
FROM
  ranks;

Output:

 rank_id | rank_name
---------+-----------
       1 | A
       2 | B

Sequence options example #

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

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

Try it

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

SELECT
  *
FROM
  ranks;

Try it

 rank_id | rank_name
---------+-----------
      10 | A

Second, insert another row into the ranks table:

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

Try it

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

SELECT
    *
FROM
    ranks;

Try it

 rank_id | rank_name
---------+-----------
      10 | A
      20 | B

Summary #

Use SQL identity column to define a column that automatically generates unique integers.

Databases #

Quiz #

Quiz

SQL Identity Column

5 questions

To understand how to use the SQL Identity column to automatically generate unique integers for each row in a table.

Was this helpful?