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.