CODEWITHSUNDEEP
alter-tableteradataidentity-column
oscilatingcretin
oscilatingcretin

Reputation: 10919

Teradata: How can you add an identity column to an existing table?

I need to add an identity column to an existing table with this SQL:

alter table app.employee 
add ID INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1 MINVALUE 0 MAXVALUE 100000000 NO CYCLE)

I can create new tables with an identity column just fine, but the above script gives me the following error:

ALTER TABLE Failed. 3706: Syntaxt error: Cannot add new Identity Column option

Teradata database is severely lacking in online support and I've only come across one option which is to basically create a copy of the table with the identity column and do a mass insert from the old table to the new one and change all references to the new table. I find it difficult to believe that this is the only possible way to do this.

What are my options here?

Upvotes: 1

Views: 9979

Answers (1)

Rob Paller
Rob Paller

Reputation: 7786

There are online resources for Teradata such as SO, Database Administrators (Stack Exhcange), and the Teradata Developer Exchange. Those are probably your best sources of information.

Page 43 of the Teradata SQL Data Definition Language - Detailed Topics Manual for Teradata 13.10 states:

You can not add an identity column to an existing base table, nor can you add the identity column attribute to an existing column.

It may seem tedious but this approach works very well and minimizes the down time of the data for the end users. It works well and is the preferred method that we use when making data model changes against tables with billions of rows. The insert of data into the new table should be fairly well optimized as the Teradata optimizer should opt for a data block level MERGE operation instead of row by row.

  1. Create new table with Identity Column
  2. Make sure target table changes to original table are held.
  3. Insert Select data from old table into new table.
  4. Rename {orig_table} as {orig_table}_old.
  5. Rename {new_table} as {orig_table}.
  6. Validate new table's data and drop {orig_table}_old
  7. Refresh view definition of 1:1 view to introduce identity column. (Optional)

Hope this helps.

Another solution that you can try if you need to expand the data type of a column that is outside of the allowable in place modification. (e.g. DECIMAL(3,0) -> DECIMAL(5,0)):

ALTER TABLE {MyTable} ADD {Orig_ColumnName}_New DECIMAL(5,0) NULL;
UPDATE {MyTable} SET {Orig_ColumnName}_New = {Orig_Column};
ALTER TABLE {MyTable} DROP {Orig_ColumnName};
ALTER TABLE {MyTable} RENAME {Orig_ColumnName}_New AS {Orig_ColumnName};
ALTER TABLE {MyTable} ADD {Orig_ColumnName} NOT NULL; /* If original column was NOT NULL */

The rules explaining the boundaries for expanding a columns data type can be found in the Teradata DDL manual.

Upvotes: 3

Related Questions