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.