1. SQL Reference Guide
  2. Table Management
  3. Altering an IDENTITY Column

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

Example 5-15 To Drop the IDENTITY Property of an Existing 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 DROP IDENTITY);