asked
Question about primary keys of a table
Today I'm going to be converting a table in our production database, which has an int column in it, to make the int column also an IDENTITY field. I asked about that [earlier in these forums][1], and got great feedback. What I'm wondering about now is the primary key, which is the IDENTITY column of my new table. Basically, this is the issue. I've duplicated the structure of the original table, but made the new table have an IDENTITY column. The old table is called TABLE, and the new table is called TABLE_NEW. According to the instructions referenced in the link I've pointed to above, after copying the data out of the old table into the new table, I then drop the old table and rename the new table. Will the primary key that's defined for my new table, stay with it after I rename it? [1]:
http://ask.sqlservercentral.com/questions/15628/setting-an-int-column-as-an-identity-column
The primary key will stay with the renamed object, yes. It won't be renamed by renaming it's parent, so if you use a naming scheme to name child objects by the name of their parent (e.g. PK_TABLE, or CK_TABLE_COLUMN) then you will need to rename those child objects manually too.
Two things... If the referenced question was considered answered please mark it as such so individuals like Oleg will continue to contribute with such detail and all members will benefit. Also, I believe you are asking if what is referenced as the PK will somehow be altered. Was the PK something other than the IDENTITY column on the original table? It is a very good candidate. The PK is defined against the table structure - not the data - and will not change while inserting data - even from another table.
