15.10 - Example: Dropping the IDENTITY Attribute From a Column Without Dropping the Column - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

Suppose you create the following identity column table named id_phone.

    CREATE TABLE id_phone(
      id_num INTEGER GENERATED ALWAYS AS IDENTITY
                    (START WITH 1000
                     INCREMENT BY 10
                     MINVALUE 0
                     MAXVALUE 300000),
      phone  INTEGER)
    UNIQUE PRIMARY INDEX(idnum);

After having used this table for some time, you now decide to use Unity Director to manage multiple Teradata Database instances, including one that includes the id_phone table. Because Unity Director requires deterministic behavior to ensure data consistency at each Teradata Database instance, you must eliminate the identity column attribute from the id_num column, but you must also retain the column and its data with id_phone because id_num is also the unique primary index for the table.

To do this, you can use the ALTER TABLE … DROP column_name IDENTITY option.

The SQL text for this ALTER TABLE request is as follows.

     ALTER TABLE id_phone
     DROP id_num IDENTITY;

Column id_num in table id_phone is no longer an identity column, but it continues to be the unique primary index for the table.