Issue while adding identity to a column in Alter table

Database

Issue while adding identity to a column in Alter table

Hi everyone,

I’m facing a problem with auto-increment/identity feature in Teradata table. I already had a table in solution without identity feature, but as the requirements changed, I have to modify/alter that column from integer data type to integer data type with identity feature, but somehow it’s not allowing me to do that. I’m not sure whether Teradata support it or not.

I had tried different approaches suggested by people over the internet.

Sample Code:

CREATE TABLE TEST
(
TEST_ID INTEGER NOT NULL,
TEST_NAME VARCHAR(32)
) ;

alter table TEST
add TEST_ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1 INCREMENT BY 1);

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

Please provide some help.

Regards,

Chirag
1 REPLY
Senior Apprentice

Re: Issue while adding identity to a column in Alter table

Hi Chirag,
the only solution is probably:

- determine the maximum value of TEST_ID in your current table

- create a copy of the table with
TEST_ID INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY(START WITH maxVal + 1 )

- insert/select the data in the new table

- drop/rename old/new table

Dieter