Previous Next JavaScript must be enabled to correctly display this content

## 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-43 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-44 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)