|
|
|
A new numeric column attribute for auto-incrementing columns is now available. This feature is enabled with the IDENTITY SQL keyword.
Syntax
exact_numeric_data_type ::
TINYINT
| SMALLINT
| INTEGER
| BIGINT
| NUMERIC
| NUMBER [ ( precision [ , scale ] ) ]
| DECIMAL [(precision, scale)]
| MONEY [(precision)]
[ IDENTITY [ ( ± seed , ± increment ) ] ]
For TINYINT, SMALLINT, INTEGER and BIGINT column types, an optional auto-incrementing attribute can be defined with the IDENTITY option. This adds the column of the defined type to the table and automatically updates the value on each row insert.
The IDENTITY attribute by itself does not guarantee uniqueness of assigned values. Use a unique index to ensure unique values.
IDENTITY can optionally specify seed and increment values. seed is the starting assignment value and is incremented by increment for each update.
CREATE TABLE t1 (name CHAR(10), id_num INTEGER IDENTITY (0, 1));
Only one IDENTITY column can be defined per table. IDENTITY columns cannot be specified on tables with only one column.
IDENTITY values assigned to aborted rows in a table are lost. Note that this can result in gaps in the numerical sequence order.
IDENTITY is not supported for NUMERIC, NUMBER, DECIMAL or MONEY column types.
IDENTITY cannot be added to an existing field via ALTER TABLE.
To return the current IDENTITY value in effect, the scalar function LAST_IDENT() can be called.
