Using T-SQL to set column to PK & Enable IDENTITY setting

Question

Using T-SQL to set column to PK & Enable IDENTITY setting

YSLGuru

SSC-Insane

Points: 21569
More actions
November 14, 2006 at 8:18 am

#153293

I am assiting a client with a problem with their DB in which a table that is suppose to have one of it's column's set as the PK (Primary Key) with the IDENTITY setting enabled (1/1). Somehow (client probably did it a while back and has forgotten) the column is not set as the PK and the IDENTITY setting is not on. I tried to construct a SQL statement to alter the tables column to set the column as the PK and to turn on the IDENTITY setting but no matter how I construct the query it will not work. I looked in BOL and if I am understanding BOL correctly, you can't enable the IDENTITY setting for an existing column. If I remove that part from the query so that it is just setting the column as the PK then it works. It only fails when trying to enable the IDENTITY setting. I made the changes via ENt Mgr and ran Profiler to see how Ent Mgr handles this and it apparently creates an entirely new table, copie sthe data from the existing table to the new table, drops the old table and renames the new table.

My question is this:
1) Can you enable the IDENTITY option on an existing column in an existing Table?
2) If yes then how?

Thanks
William
Kindest Regards,
Just say No to Facebook!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

You can always create a new column with identity, delete the old column and matching keys/objects. Then rename the old column.

Now if you need to keep the old values just let EM generate the code... cause you'll have to recreate a whole new table, enable ident insert, ship the data, disable ident insert, drop the old table and rename the new table. Also you might want to realter all objects connected to that table because all dependancies keps in sysdepends are now destroyed.

Does that clear it up?

Ian Griffiths Ten Centuries Points: 1025 More actions · Answer 2

After doing the enable ident insert - ship data - disable ident insert bit, check that you can still insert records. I was using a third party tool the other day to do exactly that and afterwards it wouldn't let me insert since it was trying to insert a duplicate primary key. I had to re-set the seed on the identity column to the highest existing value in that column and then it all worked fine. Not sure that's just a problem with the tool I used though.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Maybe not... since no records were inserted using identity function, doesn't it make sens that the seed didn't change?? I think MS most likely thaught about that which makes me believe that the software may be in error. But I am saying this without testing so I won't put my money where my mouth is .

Sureshkumar Ramakrishnan Ten Centuries Points: 1200 More actions · Answer 4

Altering the Column to have Identity column works fine when its done using Enterprise Manager.

Kindest Regards,

Sureshkumar Ramakrishnan

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Yes it does... try it on a test table and click generate script.

See all the trouble it has to go through???