I need assistance on modify table

UDA

I need assistance on modify table

here is my create table query :

create table test(

testid int check(testid < 100 and testid > 1000),
testname varchar(100) uppercase
);

this query is working fine but i want to modify the test id col for auto generation.
for this i wrote the modify query as :

alter table test modify testid int generated always as identity ( start with 1 cycle );

this query not working fine

can any one give me a solution for this plaese

Thank you
Ashok Reddy Daggula
5 REPLIES

Re: I need assistance on modify table

The Teradata alter table statement does not allow the identity column attribute to be specified or changed, either for an existing column or an added column. Identity columns can be defined only in the create table statement.

Re: I need assistance on modify table

Thanks

Is there any way to get the identity column for the created table?

can you tell me the datatype attribute like check,minvalue,maxvalue...etc?

Thanks
Regards
Ashok Reddy Daggula
N/A

Re: I need assistance on modify table

If you want an identity column on the table, you'll need to create a copy with the identity column and insert data into it.
N/A

Re: I need assistance on modify table

CREATE NEW_TABLE (IDENTIY COLUMN, REST OF COLUMNS) PRIMARY INDEX (...);
INSERT INTO NEW_TABLE (COLUMN NAMES NOT INCLUDING ID COL)
SELECT COL1, COL2 COL3....;

then you are done.

Re: I need assistance on modify table

Thanks for the replies
these will be very helpful for me