16.20 - Example: Dropping the IDENTITY Attribute From a Column Without Dropping the Column - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Syntax and Examples

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-24
dita:mapPath
wkf1512081455740.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval

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 some time, you decide to use Teradata Unity to manage multiple Teradata Database instances, including one that includes the id_phone table. Because Teradata Unity requires deterministic behavior to ensure data consistency at each Teradata Database instance, you must remove 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. You can use the following statement:
     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.