# 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:

**CREATE TABLE**(within the column definition)**ALTER TABLE (Column)****ADD COLUMN**(within the column definition)**MODIFY COLUMN**(within the column definition)**ALTER COLUMN IDENTITY**using`SET IDENTITY`or`DROP IDENTITY`

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 **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