Teradata - Generated Identity Column BY DEFAULT

Generating Values by DEFAULT:

Earlier we saw the 'GENERATED ALWAYS' option . Now we have a look at the 'GENERATED BY DEFAULT' option which  generate a value only when a value is not explicitly expressed.

CREATE        TABLE EMPLOYEE4, NO FALLBACK

(

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

EMPLNAME CHAR(20)

) ;

Unlike earlier new values will be generated only if we don’t specify the value for the IC column explicitly.

INSERT INTO EMPLOYEE4 VALUES(,'sukul');

INSERT INTO EMPLOYEE4 VALUES(NULL,'shushant');

INSERT INTO EMPLOYEE4 VALUES(6,'uma');

INSERT INTO EMPLOYEE4 VALUES(,'bhanu');

EMPLOYEEID	EMPLNAME
2	shushant
6	uma
1	sukul
3	bhanu

Note that when we provide explicit value for the 'GENERATED BY DEFAULT' column it uses that value and does not generate a value on its own. However providing a value does it override the internal sequence. That’s the reason even when we inserted employee id of 6 , next sequence did not start from 7. instead it continued its own sequence and generated 3.

Also make a note that whenever an insert fails the 'generated' value keeps on incrementing.

If the value generated if 100 when an insert fails, the next successful insert will insert value as 101.

Also just as with 'GENERATED ALWAYS', when using 'GENERATED BY DEFAULT' does not reset the column value on emptying the table.