Identity Columns

You can generate ID values using identity columns. These are similar to default values, but are dynamically evaluated.

Example
CREATE TABLE actors (id INTEGER IDENTITY,
              lastname VARCHAR(20),
              surname VARCHAR(20));

INSERT INTO actors (lastname, surname) VALUES
                    ('Pacino', 'Al'),
                    ('Willis', 'Bruce'),
                    ('Pitt', 'Brad');

SELECT * FROM actors;
Results
id lastname surname
1 Pacino Al
2 Willis Bruce
3 Pitt Brad

Usage notes for identity columns

  • If you specify an explicit value for the identity column, then this value is inserted while inserting a row.
  • If no explicit value is specified for the identity column, then Exasol generates monotonically increasing numbers as values for the Identity column. However, there are chances that gaps can occur between the numbers.
  • The current value of the number generator can be changed using ALTER TABLE (Column). Explicitly inserted values have no influence on the number generator.
  • Using the DML statements (INSERT, IMPORT, UPDATE, MERGE) you can anytime manipulate the values of an identity column.

Identity columns do not guarantee unique values and can therefore not be considered as a constraint. However, the values are unique as long as they are implicitly inserted and not manually changed.

  • Identity columns must have an exact numeric data type without any scale (INTEGER, DECIMAL(x,0)). The range for the generated values is limited by the data type.
  • A table can have only one identity column.
  • A column cannot be an identity column and have a default value at the same time.

Statements that manipulate identity columns

Identity columns can be created, changed, and dropped: 

In the following statements, the dynamically created values of an identity column are explicitly or implicitly used:

  • INSERT: DEFAULT as 'value' for a column (INSERT INTO t(i, j, k) VALUES (1, DEFAULT, 5) or implicitly in case of a column selection where the identity column is not included
  • IMPORT: implicitly in case of a column selection where the identity column is not included.
  • UPDATE: DEFAULT as 'value' for a column (SET i=DEFAULT)
  • MERGE: DEFAULT as 'value' for column within the INSERT and UPDATE operation or implicitly in the INSERT operation in case of a column selection where the identity column is not included.
  • ADD COLUMN: if the column being added is an identity column

Display of identity columns

Identity columns can be listed using the column COLUMN_IDENTITY of the system tables EXA_ALL_COLUMNS, EXA_DBA_COLUMNS, and EXA_USER_COLUMNS. The entry of the identity column is the last generated number.

Example
CREATE TABLE t (id INTEGER IDENTITY 30);
SELECT column_identity FROM exa_all_columns
             WHERE column_table='T'
             AND column_name='ID';