Db2 supports the
IDENTITY keyword to create autoincremental columns. Several options are available.
Defining a basic IDENTITY column
Db2 IDENTITY columns are integer columns. Their types can be:
Autoincremental values can be:
GENERATED ALWAYS AS IDENTITY: The user cannot force a value.
GENERATED BY DEFAULT AS IDENTITY: A value is only generated when the user doesn’t specify one.
CREATE TABLE user ( id INT GENERATED ALWAYS AS IDENTITY, username VARCHAR(50) NOT NULL, password VARCHAR(50) NOT NULL, PRIMARY KEY (id) )
Specifying sequence options
It is possible to specify options that affect the generated values. These are almost the same options that can be specified for a sequence:
START WITH: The first value that will be generated (default: 1).
INCREMENT BY: The increment to be added to generate the next value (default: 1).
MINVALUE: Values may cycle after reaching the maximum value. In this case, the
MINVALUEdetermines the value to be generated after reaching the maximum.
NO MINVALUEto disable.
MAXVALUE: The maximum allowed value.
NO MAXVALUEto disable.
NO CYCLE: Whether the values may cycle or not when
CACHE: How many next values should be kept into the cache to speed up insertions. To disable, specify
NO CACHE. Default:
- NOTE: If insertions can be made concurrently, using a cache may cause values to be created in a non-chronological order.
NO ORDER: Whether values should be generated in order. Default:
The spaces after
NO are optional. You can specify
CREATE TABLE user ( id INT GENERATED ALWAYS AS IDENTITY ( START WITH 10000 MAXVALUE 99999 NO CYCLE ), username VARCHAR(50) NOT NULL, password VARCHAR(50) NOT NULL, PRIMARY KEY (id) )
Ascending IDENTITY columns
It is possible to specify a negative
INCREMENT. In this case the first generated value will is defined by
START WITH or
MAXVALUE. If a
MINVALUE is specified, no lower values can be generated. If the values may cycle, the next generated value is determined by
id INT GENERATED ALWAYS AS IDENTITY ( START WITH 50 INCREMENT -2 MAXVALUE 100 MINVALUE 1 CYCLE ),
In this case, the first generated value is 50. The next is 48, then 46, etc. The lowest allowed value is
1, but in practice the lowest possible value is
2. The next generated value will be
Holes in IDENTITY columns
It is entirely possible to have missing numbers, or “holes”, in a
IDENTITY column. This most likely happens because some transactions failed, or were explicitly rolled back.
IDENTITY column close to its MAXVALUE: what to do
columns are normally used as primary keys. If a
column is close to its limit, we need to think how to generate new values.
However, because of the nature of
columns (they are sequences associated to a column), it is not possible to tell them to generate new values to fill the holes when needed.
IDENTITY column has the
CYCLE property, we can periodically delete the oldest values, so that they will be re-generated when needed.
If we cannot delete oldest values, we can change the data type of the
column. It’s very uncommon to need more values than those allowed by a
BIGINT is not big enough, we can use two
columns and build the primary key on both the columns:
id1 INT GENERATED ALWAYS AS IDENTITY, id2 INT GENERATED ALWAYS AS IDENTITY, PRIMARY KEY (id1, id2)
If the table is periodically completely emptied, we should use
TRUNCATE TABLE to empty it. In this way the table will be dropped and recreated from scratch, so its
columns will be reset.