Creating Tables With an IDENTITY Column

You can create an IDENTITY column when you create a table, or change an existing table to add an IDENTITY column using ALTER TABLE...ADD. In either case, choose one of the IDENTITY statements described below. This section describes creating a table with an IDENTITY column.

Here is the formal syntax for creating a table with an IDENTITY column:

GENERATED (ALWAYS | (BY DEFAULT [ON NULL])) AS IDENTITY
   [sequence_options,...]

The optional sequence_options refer to all of the Sequence Generator attributes you can supply.

IDENTITY Column Statement	Description
GENERATED ALWAYS AS IDENTITY	The sequence generator always supplies an IDENTITY value. You cannot specify a value for the column.
GENERATED BY DEFAULT AS IDENTITY	The sequence generator supplies an IDENTITY value any time you do not supply a column value.
GENERATED BY DEFAULT ON NULL AS IDENTITY	The sequence generator supplies the next IDENTITY value if you specify a NULL columnn value.

To create a table with a column GENERATED ALWAYS AS IDENTITY from the SQL CLI:

sql-> CREATE TABLE IF NOT EXISTS tname1 (
idValue INTEGER GENERATED ALWAYS AS IDENTITY, 
acctNumber INTEGER, 
name STRING, 
PRIMARY KEY (acctNumber));
Statement completed successfully
sql->

For this table, tname1, each time you add a row to the table, the Sequence Generator (SG) updates the idvalue from its cache. You cannot specify a value for idValue. If you do not specify any sequence generator attributes, the SG uses its default values.

To create a table with a column GENERATED BY DEFAULT ON NULL AS IDENTITY:

sql-> CREATE TABLE IF NOT EXISTS tname2 (
idvalue INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY, 
acctNumber INTEGER, 
name STRING, 
PRIMARY KEY (acctNumber));
Statement completed successfully
sql->

For this table, tname2, each time you add a row, the SG inserts the next available value from its cache if no value is supplied for the idvalue column, the supplied value for the idvalue column is NULL.

To create a table with a column GENERATED BY DEFAULT AS IDENTITY:

sql-> CREATE TABLE IF NOT EXISTS tname3 (
idvalue INTEGER GENERATED BY DEFAULT AS IDENTITY, 
acctNumber INTEGER, 
name STRING, 
PRIMARY KEY (acctNumber));
Statement completed successfully
sql->

For this table, tname3, each time you add a row, the SG inserts the next available value from its cache if no value is supplied for the idvalue column.

To create a new table, sg_atts, with several SG attributes:

sql-> CREATE Table sg_atts (
id INTEGER GENERATED ALWAYS AS IDENTITY
(START WITH 2 
INCREMENT BY 2 
MAXVALUE 200 
NO CYCLE),
name STRING,
PRIMARY KEY (id));
Statement completed successfully
sql->

The table sg_atts specifies that the integer IDENTITY field (id) is generated always.

SG Attribute	Description
start with 2	Start the sequence value at 2.
increment by 2	Increment the sequence value by 2 for each row.
maxvalue 200	Specifies the maximum IDENTITY value. What you specify overrides the default value maxvalue, which is the upper bound of the IDENTITY datatype in use. Once the IDENTITY column reaches this value, 200, the SG will not generate any more IDENTITY values. The maximum value has been reached and the no cycle attribute is in use.
no cycle	Do not restart from 2 or with any value at all, once the column reaches the maxvalue.

To create another table, sg_some_atts, with some SG attributes:

sql-> CREATE Table sg_some_atts (
id LONG GENERATED BY DEFAULT AS IDENTITY
(START WITH 1
INCREMENT BY 1 
CYCLE 
CACHE 200),
account_id INTEGER,
name STRING,
PRIMARY KEY (account_id));

For the sg_some_atts table, specify an id column GENERATED BY DEFAULT AS IDENTITY, but which is not the primary key.

SG Attribute or Other Detail	Description
CYCLE	Specifying CYCLE indicates that the SG should supply IDENTITY values up to either the MAXVALUE attribute you specify, or the default MAXVALUE. When the IDENTITY reaches the MAXVALUE value, the SG restarts the values over, beginning with MINVALUE, if it is specified, or with the default MINVALUE for the data type. CYCLE is orthogonal to the CACHE attribute, which indicates only how many values to store in local cache for swift access. You can set CACHE value to closely reflect the maximum value of the datatype, but we do not recommend this, due to the client cache size.
CACHE 200	The number of values that each client stores in its cache for fast retrieval. When the IDENTITY reaches the last number in the cache, the SG gets another set of values from the server automatically.
START WITH 1	The SG generates values 1, 2, 3 and so on, until it reaches the maximum value for a LONG data type.
INCREMENT BY 1	The SG increments each new IDENTITY value for every new row.

For a full list of all sequence generator attributes, see Sequence Generator.