Teradata - Generated Identity Columns - Rules

Rules for using Identity columns:

1. Identity column(IC) is considered as column attribute. We cannot drop or modify the IC attribute of the column. But we can drop the IC column from the table.

2. An IC column cannot exist along with following attributes:
    
• DEFAULT
• BETWEEN
• COMPRESS
• CHECK
• REFERENCES

3. There can be only one IC column

4. IC column cannot be a part of any of the following:

• Composite indexes (primary or secondary)
• Hash or Join Indexes

• Partitioned Primary Indexes
• Value-ordered Indexes

Note that IC column cannot be  apart of Composite index, but it can be the index as a whole.

Following statement is not allowed:

CREATE        TABLE EMPLOYEE4, NO FALLBACK

(

EMPLOYEEID INTEGER GENERATED BY DEFAULT AS IDENTITY (MAXVALUE 4 CYCLE MINVALUE 1),

EMPLNAME CHAR(20)

) PRIMARY INDEX (EMPLOYEEID,EMPLNAME); --> composite PI with IC column as part of the PI

This would fail with the error code: CREATE TABLE Failed. 5784:  Illegal usage of Identity Column EMPLOYEEID. 

However following is allowed:

CREATE        TABLE EMPLOYEE4, NO FALLBACK

(

EMPLOYEEID INTEGER GENERATED BY DEFAULT AS IDENTITY (MAXVALUE 4 CYCLE MINVALUE 1),

EMPLNAME CHAR(20)

) PRIMARY INDEX (EMPLOYEEID); --> IC column as PI itself.

4. Column compression is not allowed on IC columns.

5. IC are not supported by load utilities like Fast load or multi load.

6. IC can be used with multisession BTEQ or Multistatement TPUMP

7. Bulk inserts done via INSERT SELECT also cannot guarantee that the sequence of the assigned IC's will be unbroken. This is because each AMP pre-allocates a range of numbers based on a pre-defined interval (specified in the DBS Control Record). Consequently each AMP will provide its own sequence independently of the others.

8. Imp: 'CREATE TABLE AS' feature does not work with Identity columns.

In the below example EMPLOYEE4 is a table with IC. We try to create another table EMPLOYEE5 as follows and the query fails

CREATE TABLE EMPLOYEE5 AS EMPLOYEE4 WITH DATA ;

Error Code: CREATE TABLE Failed. 5788:  CREATE TABLE AS currently does not support Identity Columns.