0

I have created couple of tables using the Sybase Central tool in Sybase ASE 15.5 (Sybase AnyWhere). I have defined a column as a primary key (int data type) and somehow the column has become Identity as well.

Now from Sybase Central, there is no way I can remove the Identity from that column, even if there is no data in this table or in any of the referenced tables.

Can anybody help? I don't want to use Set IDENTITY_INSERT, I want to remove the identity behavior altogether from this column.

Thanks

Danny Beckett
  • 20,529
  • 24
  • 107
  • 134
conceptSeeker
  • 729
  • 1
  • 7
  • 7
  • Have you tried creating the table manually via isql? It could be a behavior related to using Sybase Central. – user1505078 Aug 24 '12 at 22:34
  • Creating tables from iSql works just fine. My question is can I drop the identity behavior of a primary key column in sybase (not by using set IDENTITY_INSERT, which just sets it off, not removes the behavoir). – conceptSeeker Aug 25 '12 at 07:31

1 Answers1

3

Your question is a little confusing, as I'm not sure what Sybase software, or software version you are using. Sybase ASE 15.5 is not the same as Sybase SQL Anywhere, but hopefully these steps will work regardless.

You can not remove the identity behavior from a column, but you can alter the table to accomplish the same thing. Here are the steps you should take to preserve your data. Ensure there are no indexes on the table.

  • Alter the table to add a new column with the same datatype as the current identity column.
  • Copy the data from the identity column to the new column.
  • Drop the identity column

  • (Optional)If you've written any code against the table, you will probably want to rename the new column to the same name as the column that was just dropped.

    alter table TABLE_NAME add NEW_COL int NULL
go
update TABLE_NAME set NEW_COL = ID_COL_NAME
go
alter table TABLE_NAME drop ID_COL_NAME
go
alter table TABLE_NAME rename NEW_COL to ID_COL_NAME
go
Mike Gardner
  • 6,611
  • 5
  • 24
  • 34