All Forums UDA
daggula 5 posts Joined 02/11
11 Feb 2011
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

Jim Chapman 449 posts Joined 09/04
11 Feb 2011

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.

daggula 5 posts Joined 02/11
12 Feb 2011

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

mnylin 118 posts Joined 12/09
14 Feb 2011

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.

emilwu 72 posts Joined 12/07
14 Feb 2011

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.

daggula 5 posts Joined 02/11
14 Feb 2011

Thanks for the replies
these will be very helpful for me

You must sign in to leave a comment.