Previous Next JavaScript must be enabled to correctly display this content

## 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.

Example 5-20 Create a table with a column GENERATED ALWAYS AS IDENTITY from the SQL CLI

    CREATE TABLE IF NOT EXISTS tname1 (
    idValue INTEGER GENERATED ALWAYS AS IDENTITY,
    acctNumber INTEGER,
    name STRING,
    PRIMARY KEY (acctNumber))

Output:

    Statement completed successfully

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.

Example 5-21 Create a table with a column GENERATED BY DEFAULT ON NULL AS IDENTITY

    CREATE TABLE IF NOT EXISTS tname2 (
    idvalue INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
    acctNumber INTEGER,
    name STRING,
    PRIMARY KEY (acctNumber))

Output:

    Statement completed successfully

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.

Example 5-22 Create a table with a column GENERATED BY DEFAULT AS IDENTITY

    CREATE TABLE IF NOT EXISTS tname3 (
    idvalue INTEGER GENERATED BY DEFAULT AS IDENTITY,
    acctNumber INTEGER,
    name STRING,
    PRIMARY KEY (acctNumber))

Output:

    Statement completed successfully

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.

Example 5-23 create a new table, `sg_atts`, with several SG attributes

    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))

Output:

    Statement completed successfully

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`.

Example 5-24 create another table, `sg_some_atts`, with some SG attributes

    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.