Identity Columns

Version

12.1.0.1
Categories

SQL

The identity columns feature can be used to designate one column in the table as the identity for the row. The database will automatically assign an increasing integer value from a sequence generator to the identity column for each subsequent INSERT statement. This feature is sometimes also referred to as autoincrement.

This feature is part of the ISO SQL:2016 standard.

To declare a column as an identity in a table, use the GENERATED AS IDENTITY column attribute:

-- Create a table with the column "ID" generated as Identity column
CREATE TABLE employees
(
   id           NUMBER        GENERATED AS IDENTITY NOT NULL PRIMARY KEY,
   first_name   VARCHAR2(10),
   last_name    VARCHAR2(10)                        NOT NULL,
   job_title    VARCHAR2(20)                        NOT NULL
);

-- Note the absence of the "ID" column in the INSERT statement.
-- Although it is a primary key and NOT NULL, the inserts succeed.
INSERT INTO employees (first_name, last_name, job_title)
  VALUES ('Gerald', 'Venzl', 'Developer');

INSERT INTO employees (first_name, last_name, job_title)
  VALUES ('Andres', 'Almiray', 'Developer');

INSERT INTO employees (first_name, last_name, job_title)
  VALUES ('Chris', 'Saxon', 'Developer Evangelist');

COMMIT;

-- The SELECT statement will show the "ID" column with values filled by the generated identity values.
SELECT * FROM employees;

The identity column provides additional syntax modifiers to:

  • Always generate a new identity value, via GENERATED ALWAYS AS IDENTITY

  • Generate a new identity value if none has been provided, via GENERATED BY DEFAULT

  • Generate a new identity value if one has been provided by evaluates to NULL, via GENERATED BY DEFAULT ON NULL

  • Provide additional identity generation options via GENERATED AS IDENTITY (<identity_options>)

Result
SQL> -- Create a table with the column "ID" generated as Identity column
SQL> CREATE TABLE employees
  2  (
  3     id           NUMBER        GENERATED AS IDENTITY NOT NULL PRIMARY KEY,
  4     first_name   VARCHAR2(10),
  5     last_name    VARCHAR2(10)                        NOT NULL,
  6     job_title    VARCHAR2(20)                        NOT NULL
  7 );

Table EMPLOYEES created.

SQL> -- Note the absence of the "ID" column in the INSERT statement.
SQL> -- Although it is a primary key and NOT NULL, the inserts succeed.

SQL> INSERT INTO employees (first_name, last_name, job_title)
  VALUES ('Gerald', 'Venzl', 'Developer');

1 row inserted.

SQL> INSERT INTO employees (first_name, last_name, job_title)
  VALUES ('Andres', 'Almiray', 'Developer');

1 row inserted.

SQL> INSERT INTO employees (first_name, last_name, job_title)
  VALUES ('Chris', 'Saxon', 'Developer Evangelist');

1 row inserted.

SQL> COMMIT;

Commit complete.

SQL> -- The SELECT statement will show the "ID" column with values filled by the generated identity values.
SQL> SELECT * FROM employees;

ID FIRST_NAME LAST_NAME JOB_TITLE
-- ---------- --------- --------------------
 1 Gerald     Venzl     Developer
 2 Andres     Almiray   Developer
 3 Chris      Saxon     Developer Evangelist

Benefits

The first relational normal form dictates that each table has to have a primary key by which a row can be uniquely identified (i.e. only one row exists for a given primary key value). Sometimes data records contain "natural primary keys" that uniquely identify themselves, such as the user name for a customer_accounts table, the VIN (Vehicle Identification Number) for a cars table, the driver license id for a driver _licenses table. However, often such natural primary keys do not exist within the data records and need to instead be generated, which is where identity columns can help.

Identity columns ensure that the value for a new row will always be unique by using an always incrementing integer value that will never be used twice.

Further information