Identity Columns

Using identity columns you can generate ids. They are similar to default values, but evaluated dynamically.

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;

ID 			LASTNAME 		SURNAME
-------------------    --------------------    --------------------
			1 Pacino 		Al
			2 Willis 		Bruce
			3 Pitt 			Brad

The following are some of the considerations for identity column:

  • 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.

Note: Identity columns cannot be considered as a constraint, that is, identity columns do not guarantee unique values. However, the values are unique as long as they are inserted implicitly and are 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:

  • INSERTDEFAULT 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 Metadata System Tables. 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';

COLUMN_IDENTITY
-------------------------------------
				   30