Example: INSERT and GENERATED ALWAYS Identity Columns - Teradata Database

Example: INSERT and GENERATED ALWAYS Identity Columns

Assume that column_1 of newly created table_1 is an identity column defined as GENERATED ALWAYS. The following INSERT statements automatically generate numbers for column_1 of the inserted rows, even if they specify a value for that column.

     INSERT INTO table_1 (column_1, column_2, column_3) 

     VALUES (111,111,111);

     INSERT INTO table_1 (column_1, column_2, column_2) 

     VALUES (,222,222);

Check the inserts by selecting all the rows from table_1.

     SELECT *

     FROM table_1;

     *** Query completed. 2 rows found. 3 columns returned.

     *** Total elapsed time was 1 second.

      column_1  column_2  column_3

     --------- --------- ---------

             2       111       111

             1       222       222

Note the following things about this result:

Even though the value 111 was specified for column_1 in the first insert, the value is rejected and replaced by the generated value 2 because column_1 is defined as GENERATED ALWAYS.

A warning is returned to the requestor when a user-specified value is overridden by a system-generated number.

The first number in the identity column sequence is not necessarily allocated to the first row inserted because of parallelism.