2

I have a table which already consists a number of records. The table has a column named 'id' of integer type and I have already inserted unique values in id column for all the records in the table.

Now I need to set IDENTITY constraint on the 'id' column so that in any new record added to the table, the value of id column gets inserted automatically incremented from the last value added to the column.

The create table query of the table is like follows: 

create table Table1 (
    Column1                         varchar(255)                     not null,
    Column2                         varchar(254)                     not null,
    Column3                         int                              not null,
    id                              int                              ,
 PRIMARY KEY CLUSTERED ( id )  on 'default' 
)

I insert data into the table using following query:

Insert into Table1 (Column1, Column2, Column3, id) values("abc","def",12,1)

But when I try to execute following Alter table query on my sybase database it returns an error "Incorrect Syntax near 'IDENTITY'" 

ALTER TABLE Table1 MODIFY id int IDENTITY DEFAULT AUTOINCREMENT NOT NULL

Can anyone point me in the right direction about, how to apply IDENTITY constraint on a column which already has data?

user1837698
  • 33
  • 3
  • Did you try looking at other answers? http://stackoverflow.com/questions/20958702/alter-exisitng-int-column-to-identity-in-sybase or http://stackoverflow.com/questions/29878276/add-row-number-to-a-new-column/29908392#29908392 – Mike Gardner Feb 17 '16 at 15:11
  • This link says that we have to add another column as row sequence but I don't want to add any new column, I want to alter existing column. – user1837698 Feb 18 '16 at 05:57
  • You can't. The closest you can get is the first [answer](http://stackoverflow.com/questions/20958702/alter-exisitng-int-column-to-identity-in-sybase/20978521#20978521). You can add an identity column, assign its values from an existing column, drop the old column, and rename the new identity column. Or, create a new table with the proper id column and copy the data from your old table, then drop the old table and rename the new table. You may not want to do it, but those are your options. – Mike Gardner Feb 18 '16 at 13:52

0 Answers0