Teradata - Generated Identity Column ALWAYS - START WITH,MAXVALUE,MINVALUE,INCREMENT BY,CYCLE

Using ALWAYS, MINVALUE, MAXVALUE and CYCLE:

For test purpose we create a following table:

CREATE        TABLE EDW_RESTORE_TABLES.EMPLOYEE4, NO FALLBACK

(

EMPLOYEEID INTEGER GENERATED ALWAYS AS IDENTITY (MAXVALUE 4),

EMPLNAME CHAR(20)

) ;

Then we run following queries:

/* NULL using positional assignment*/

INSERT INTO EMPLOYEE4 VALUES(,'sukul');

/* providing NULL explicitly*/

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

/*passing a value explicitly for identity column. These values will be ignored*/

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

INSERT INTO EMPLOYEE4 VALUES(1,'bhanu');

select * from EmPLOYEE4;

EMPLOYEEID	EMPLNAME
1	sukul
2	shushant
3	uma
4	bhanu

Note that when we use 'ALWAYS' the explicitly specified values for the identity column are ignored..

Now we try to insert one more row as follows:

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

This query fails because of following reason: INSERT Failed. 5753:  Numbering for Identity Column EMPLOYEEID is over its limit. 

The reason the max value of 4 is already reached and we cannot insert more rows to the table now.

Things learnt : once the max value is reached we cannot new rows to the table(unless we have CYCLE option specified).

We can try to delete all the existing rows and then try to insert a new row using following queries:

DELETE FROM EMPLOYEE4;
INSERT INTO EMPLOYEE4 VALUES(NULL,'himanshu');

Even after deleting all rows the new insert fails with the same error code:  INSERT Failed. 5753:  Numbering for Identity Column EMPLOYEEID is over its limit. 

Things Learnt: Once Max value is reached (and CYCLE option is not specified) we cannot insert a new row even after deleting all the records from the table. This  means deleting data from table does not reset the max value reached.

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

Now we will drop the above table and recreate it using the CYCLE option as follows:

DROP table EMPLOYEE4;
 

CREATE        TABLE EMPLOYEE4, NO FALLBACK

(

EMPLOYEEID INTEGER GENERATED ALWAYS AS IDENTITY (MAXVALUE 4 CYCLE),

EMPLNAME CHAR(20)

) ;

And then make 5 inserts as below:

INSERT INTO EMPLOYEE4 VALUES(,'sukul');

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

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

INSERT INTO EMPLOYEE4 VALUES(1,'bhanu');

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

SELECT * FROM EMPLOYEE4;

EMPLOYEEID	EMPLNAME
-2147483647	himanshu
1	sukul
2	shushant
3	uma
4	bhanu

Note that after we hit the max value of 4 , as we used the CYCLE option it goes to the minimum value.

Since we had not specified the min value it uses the minimum value supported by the data type an hence we get -2147483647 after rolling over from 4.

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

Assume that we have a table defined as follows:

CREATE        TABLE EMPLOYEE4, NO FALLBACK

(

EMPLOYEEID INTEGER GENERATED ALWAYS AS IDENTITY (STArt WITH 100 INCREMENT BY 2 MAXVALUE 104 CYCLE MINVALUE 10),

EMPLNAME CHAR(20)

) ;

And then we make following inserts:

INSERT INTO EMPLOYEE4 VALUES(,'sukul');

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

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

INSERT INTO EMPLOYEE4 VALUES(1,'bhanu');

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

Following is how the table looks like:

EMPLOYEEID	EMPLNAME
10	bhanu
12	himanshu
100	sukul
102	shushant
104	uma

Note that 'Start WITH' indicates the value to begin with. In this case its 100. INCREMENT BY indicates the value by which the value should be increment.  In this case it 2.

Max value is 104 and min value is 10. When the value rolls over 104, it goes to 10(as min value is specified ,it does not go to data type default). Thus note that it can start with a value greater than the min value.

Care needs to be taken that CYCLE option should not create uniqueness violation or duplicate rows in the table. If there is a UPI defined on the generated column only then CYCLE option can be dangerous as it will inserts to  be rejected. Even if the define the PI as NUPI we need to take care that we don’t create exactly identical rows as they will be rejected by SET tables.

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

Assume a table as below:

CREATE        TABLE EMPLOYEE4, NO FALLBACK

(

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

EMPLNAME CHAR(20)

) ;

INSERT INTO EMPLOYEE4 VALUES(,'sukul');

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

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

select * from EmPLOYEE4;

EMPLOYEEID	EMPLNAME
1	sukul
2	shushant
3	uma

Now  we delete a row with employeeid=2

DELETE FROM EMPLOYEE4 where employeeid=2;

And then insert another row as follows:

INSERT INTO EMPLOYEE4 VALUES(1,'bhanu');

Following is how the table will look like :

EMPLOYEEID	EMPLNAME
1	sukul
3	uma
4	bhanu

Thing learnt: Gaps are never filled. The column knows what was the last value and what value to put next.

Now we try to insert another row as follows

INSERT INTO EMPLOYEE4 VALUES(,'sukul');

The insert fails because of following reason: INSERT Failed. 2802:  Duplicate row error in EDW_RESTORE_TABLES.EMPLOYEE4. 

The reason is that the table is a set table and does not allow duplicate rows. Duplicate was generated because value got rolled off and came back to the minimum value of 1. And the table already had row of 1,'sukul'

Now we insert another row as follows:

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

select * from EmPLOYEE4;

EMPLOYEEID	EMPLNAME
2	himanshu
4	bhanu
1	sukul
3	uma

Note that new record got the employee-id as 2. The earlier insert had failed, but still the value kept on incrementing.