Previous Next JavaScript must be enabled to correctly display this content

## Altering an IDENTITY Column

Use the `ALTER TABLE...MODIFY` clause to change one or more attributes of a table's IDENTITY column and its Sequence Generator (SG) options.

Note:

The `MODIFY` clause in an `ALTER TABLE`... statement is supported only on IDENTITY columns.
There are two ways to alter an IDENTITY column:

- The property of the IDENTITY column can be altered. Additionally, the sequence generator attributes associated with an IDENTITY column can be altered. The sequence generator is modified immediately with the new attributes, however, a client will see the effects of the new attributes on the sequence numbers generated on subsequent requests by the client to the sequence generator, which will happen when the cache is used up or the attributes stored at the client time out.

  Note:

  Each client has a time-based cache to store the sequence generator attributes. The client connects to the server to refresh this cache after it expires. The default timeout is 5 mins and it can be changed by setting `sgAttrsCacheTimeout` in KVStoreConfig.
- The IDENTITY property of an existing IDENTITY column can be dropped. The sequence generator attached to that IDENTITY column is also removed. The system will no longer generate a value for that column.

The following example shows how to alter the property of the identity column id from GENERATED ALWAYS to GENERATED BY DEFAULT and altering sequence generator attributes START WITH, INCREMENT BY, MAXVALUE and CACHE.

Example 5-39 To Alter the Property and Sequence Generator Attributes of an IDENTITY Column

    CREATE Table Test_alter (
    id INTEGER GENERATED ALWAYS AS IDENTITY
     (START WITH 1
     INCREMENT BY 2
     MAXVALUE 100
     CACHE 10
     CYCLE),
     name STRING, PRIMARY KEY (id)
    )

    ALTER TABLE Test_alter (MODIFY id GENERATED BY DEFAULT AS IDENTITY
    (START WITH 1000
     INCREMENT BY 3
     MAXVALUE 5000
     CACHE 1
     CYCLE)
    )

Explanation: The IDENTITY property is modified. Also, the sequence generator is modified immediately with the new attributes, however you will see the effects of the new attributes on the generated sequence numbers only after the cache is used up or the attributes stored at the client time out..
Output: The IDENTITY property and the sequence generator attributes are modified.

    Statement completed successfully

Example 5-40 To Drop the IDENTITY Property of an Existing IDENTITY column

    CREATE Table Test_alter_table (
    id INTEGER GENERATED ALWAYS AS IDENTITY(
    START WITH 1
    INCREMENT BY 2
    MAXVALUE 100
    CACHE 10
    CYCLE),
    name STRING,
    PRIMARY KEY (id))

    ALTER TABLE Test_alter_table (MODIFY id DROP IDENTITY)

Explanation: The IDENTITY property and the sequence generator are dropped. You must supply a value for column `id` while inserting rows into the table.
Output: The IDENTITY property and the sequence generator associated with the column `id` are dropped.

    Statement completed successfully