February 4, 2009 at 10:04 am
Hi All,
Is it possible to make a column as identity, after created the same table.
For Ex, we have created a new table with Primary key constraint. But later we found that we missed that primary key column as an identity column. Furthermore, that primary key column has an references with some other table (Foreign Key on some other table).
In that time, is it possible to make that primary key column as an identity???
We disabled that foreign key but bad luck! Any idea how to sole that.
Appreciate your help!!!
--- 😛
February 4, 2009 at 11:06 am
Just go into management studio into the table design and set the Identity Specification and it should work just fine
February 4, 2009 at 11:06 am
The foreign key won't make any difference
February 4, 2009 at 9:29 pm
Andrew Reilly (2/4/2009)
Just go into management studio into the table design and set the Identity Specification and it should work just fine
😛 That i know. How can i achieve through script?
February 4, 2009 at 9:34 pm
as mentioned previously, you can add identity column and still use the exisiting primary key.
If you really want to add identity column to use as primary key, add it to existing primary key and use it as a composite primary key. in turn then you have to update all the tables referencing to this base table. i would recommend just to add identity column and keep on using your existing primary key column.
February 5, 2009 at 2:54 am
Ok I see what you want now
Create a copy of the table with the identity field set
Copy all of the data across from the old table
Drop the foreign key on the old table
Drop the old table
Rename the new table
Put the foreign key back on
Job done 🙂
February 5, 2009 at 10:10 pm
Thanks All for your valuable reply.
Yes Andrew, i have done that through dropping the foreign key and recreating the same.
But i am still doing, is there any other way to make this feasible.
Why because there are 8 tables which are referring the master table (identity key table). so almost 16 lines of code i have to do.
Could you tell me any way other that this?
---
February 6, 2009 at 8:21 am
Other way is to edit the table design in SSMS (don't save it) & just save the generated script, though I never tried this myself.
--Ramesh
February 6, 2009 at 8:24 am
Or if you have something like SQL compare make the changes on a test server and use SQL compare to do a comparison and script the changes out
February 6, 2009 at 9:01 am
There is no way u can achieve this with script unless you add a separate column with IDENTITY as a property.
Thanks
Vijaya Kadiyala
February 6, 2009 at 3:08 pm
Vijaya - I'm confused why not ? Am I missing something
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply