Add or Remove an IDENTITY column

An existing table can be altered and an IDENTITY column can be added. An existing IDENTITY column can also be removed from a table.

Adding an IDENTITY Column to an Existing Table

Use ALTER TABLE to add an IDENTITY column to an existing table.

Example 5-41 Add an IDENTITY column

Create a table, test_alter, without an IDENTITY column:

CREATE Table test_alter 
 (id INTEGER,
 name STRING, 
 PRIMARY KEY (id))

Output:

Statement completed successfully

Use ALTER TABLE to add an IDENTITY column to test_alter. Also specify several Sequence Generator (SG) attributes for the associated new_id IDENTITY column, but do not use the IDENTITY column as a PRIMARY KEY:

ALTER Table Test_alter 
(ADD new_id INTEGER GENERATED ALWAYS AS IDENTITY 
 (START WITH 1 
 INCREMENT BY 2 
 MAXVALUE 100 
 CACHE 10 
 CYCLE))

Output:

Statement completed successfully

Note:

To add an IDENTITY column to a table, the table must be at a top level. You cannot add an IDENTITY column as the column of a deeply embedded structured datatype. Adding a column does not affect the existing rows in the table, which get populated with the new column’s default value (or NULL).

Dropping an IDENTITY Column

To remove an IDENTITY column from a table, so no such field remains, use the ALTER TABLE statement.

Example 5-42 Drop an IDENTITY column

Create a table, test_alter, with 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 (name))

To drop the id column, use the ALTER TABLE statement with a DROP id clause.

ALTER TABLE Test_alter (DROP id)