Identity column attributesFor SMALLINT,
INT, and BIGINT columns with identity attributes, automatically
assigns increasing integer values to the column. Identity column attributes
behave like other defaults in that when an insert statement does not specify
a value for the column, automatically
provides the value. However, the value is not a constant; automatically
increments the default value at insertion time.
The IDENTITY keyword
can only be specified if the data type associated with the column is one of
the following exact integer types.
There are two kinds of identity columns in :
those which are GENERATED ALWAYS and those which are GENERATED BY DEFAULT.
- GENERATED ALWAYS
- An identity column that is GENERATED ALWAYS will increment the default
value on every insertion and will store the incremented value into the column.
Unlike other defaults, you cannot insert a value directly into or update an
identity column that is GENERATED ALWAYS. Instead, either specify the DEFAULT
keyword when inserting into the identity column, or leave the identity column
out of the insertion column list altogether. For example:create table greetings
(i int generated always as identity, ch char(50));
insert into greetings values (DEFAULT, 'hello');
insert into greetings(ch) values ('bonjour');Automatically generated
values in a GENERATED ALWAYS identity column are unique. Creating an identity
column does not create an index on the column.
- GENERATED BY DEFAULT
An identity column that is GENERATED BY DEFAULT will only increment
and use the default value on insertions when no explicit value is given. Unlike
GENERATED ALWAYS columns, you can specify a particular value in an insertion
statement to be used instead of the generated default value.
To use
the generated default, either specify the DEFAULT keyword when inserting into
the identity column, or just leave the identity column out of the insertion
column list. To specify a value, included it in the insertion statement. For
example:create table greetings
(i int generated by default as identity, ch char(50));
-- specify value "1":
insert into greetings values (1, 'hi');
-- use generated default
insert into greetings values (DEFAULT, 'salut');
-- use generated default
insert into greetings(ch) values ('bonjour'); Note that unlike
a GENERATED ALWAYS column, a GENERATED BY DEFAULT column does not guarantee
uniqueness. Thus, in the above example, the hi and salut rows
will both have an identity value of "1", because the generated column starts
at "1" and the user-specified value was also "1". To prevent duplication,
especially when loading or importing data, create the table using the START
WITH value which corresponds to the first identity value that the system should
assign. To check for this condition and disallow it, you can use a primary
key or unique constraint on the GENERATED BY DEFAULT identity column.
By default, the initial value of an identity column
is 1, and the amount of the increment is 1. You can specify non-default values
for both the initial value and the interval amount when you define the column
with the key words START WITH and INCREMENT BY. And if you specify a negative
number for the increment value, decrements the
value with each insert. If this value is positive, increments
the value with each insert. A value of 0 raises a statement exception.
The maximum and minimum values allowed in
identity columns are determined by the data type of the column. Attempting
to insert a value outside the range of values supported by the data type raises
an exception.
Maximum and Minimum Values for Columns
with Generated Column Specs
Data type
Maximum Value
Minimum Value
SMALLINT
32767 (java.lang.Short.MAX_VALUE)
-32768 (java.lang.Short.MIN_VALUE)
INT
2147483647 (java.lang.Integer.MAX_VALUE)
-2147483648 (java.lang.Integer.MIN_VALUE)
BIGINT
9223372036854775807 (java.lang.Long.MAX_VALUE)
-9223372036854775808 (java.lang.Long.MIN_VALUE)
Automatically generated values in an identity column are unique.
Use a primary key or unique constraint on a column to guarantee uniqueness.
Creating an identity column does not create an index on the column.
The IDENTITY_VAL_LOCAL function
is a non-deterministic function that returns the most recently assigned value
for an identity column. See for
more information.
Specify the schema, table, and column name using
the same case as those names are stored in the system tables--that is, all
upper case unless you used delimited identifiers when creating those database
objects. keeps
track of the last increment value for a column in a cache. It also stores
the value of what the next increment value will be for the column on disk
in the AUTOINCREMENTVALUE column of the SYS.SYSCOLUMNS system
table. Rolling back a transaction does not undo this value, and thus rolled-back
transactions can leave "gaps" in the values automatically inserted into an
identity column. behaves
this way to avoid locking a row in SYS.SYSCOLUMNS for the duration
of a transaction and keeping concurrency high.
Identity
columns and triggersWhen an insert happens
within a triggered-SQL-statement, the value inserted by the triggered-SQL-statement
into the identity column is available from ConnectionInfo only within
the trigger code. The trigger code is also able to see the value inserted
by the statement that caused the trigger to fire. However, the statement that
caused the trigger to fire is not able to see the value inserted by the triggered-SQL-statement
into the identity column. Likewise, triggers can be nested (or recursive).
An SQL statement can cause trigger T1 to fire. T1 in turn executes an SQL
statement that causes trigger T2 to fire. If both T1 and T2 insert rows into
a table that cause to
insert into an identity column, trigger T1 cannot see the value caused by
T2's insert, but T2 can see the value caused by T1's insert. Each nesting
level can see increment values generated by itself and previous nesting levels,
all the way to the top-level SQL statement that initiated the recursive triggers.
You can only have 16 levels of trigger recursion.