17.05 - Example: Dropping the IDENTITY Attribute From a Column Without Dropping the Column - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1144-175K
Language
English (United States)

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 Vantage instances, including one that includes the id_phone table. Because Teradata Unity requires deterministic behavior to ensure data consistency at each Vantage 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.