CODEWITHSUNDEEP
sqlsql-server-2008auto-increment
espresso_coffee
espresso_coffee

Reputation: 6110

How to replace Column char(10) to int auto_increment in SQL 2008 table?

I'm working on the project where one of my tables used Fox Pro in the past. They have used server side programming language (ColdFusion) to create unique ID for each record in the table. With SQL auto increment field I don't need this technique and I would like to set my key column to INT auto increment. I was wondering what is the best way to modify key column in my table? Here is example of my table design:

Column Name  Data Type  Allow Nulls
im_key       char(10)   No

I can't simply change the type to INT and set auto increment because my current type is char. If anyone can help please let me know. Thank you.

Upvotes: 1

Views: 137

Answers (1)

HoneyBadger
HoneyBadger

Reputation: 15140

You can add a new column im_key_ident, set it as identity. Rename the old column: sp_rename 'table_name.im_key', 'im_key_old', 'COLUMN';. Finally, you can rename the new column: sp_rename 'table_name.im_key_ident', 'im_key', 'COLUMN';. You can choose to keep or to drop the old column.

If the im_key column is currently used as primary key, foreign key, in functions, procedures or in any other way, it will become very cumbersome, very fast...

Upvotes: 1

Related Questions