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]Code language: SQL (Structured Query Language) (sql)

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

 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');Code language: SQL (Structured Query Language) (sql)

Try it

The database system issued the following error:

ERROR:  cannot insert a non-DEFAULT value into column "rank_id"Code language: SQL (Structured Query Language) (sql)

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
);Code language: SQL (Structured Query Language) (sql)

Try it

Third, insert a row into the ranks table:

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

Try it

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 INSERT statement also works.

Finally, retrieve data fro 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
); 
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

Summary #

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

Databases #

Quiz #

Was this tutorial helpful ?