September 21, 2015 at 2:14 pm
I'm working on creating a new version of an existing table. I want to keep the old table around, only with a different name. In looking this up I found there's a system routine named sp_rename, which looks like it will work fine.
However in thinking about this I realized that the foreign key constraints defined on the table (there's 3 of them) are likely to not be renamed, correct?
If I'm correct, then I suppose I could rename the table, then drop the 3 foreign key constraints, and create them new using different names for those foreign key constraints. I'm just wanting to checking my thinking to make sure I haven't missed anything.
Kindest Regards, Rod Connect with me on LinkedIn.
September 21, 2015 at 2:24 pm
you are correct, only the table would be renamed.
you can rename each of the constraints, separately, one at a time, if you wanted with sp_rename, which you had already identified.
Lowell
September 21, 2015 at 2:34 pm
I hadn't thought of renaming the foreign key constraints. That's an interesting idea. However, even if I did rename the foreign key constraints they're definition would still be looking for a table that no longer exists. I'm thinking it might be better to drop the foreign key constraints, rename the table, and then re-create the foreign key constraints only this time using the name of the renamed table. Does that make sense?
Kindest Regards, Rod Connect with me on LinkedIn.
September 21, 2015 at 2:38 pm
Rod at work (9/21/2015)
I hadn't thought of renaming the foreign key constraints. That's an interesting idea. However, even if I did rename the foreign key constraints they're definition would still be looking for a table that no longer exists. I'm thinking it might be better to drop the foreign key constraints, rename the table, and then re-create the foreign key constraints only this time using the name of the renamed table. Does that make sense?
underneath the covers, the foreign keys point to object_id, and NEVER names, so when you renamed the table, the object_id stayed the same.
that's why SQL can support renaming objects....it's the ID's all constraints point to, not the name.
if you script out the freshly renamed table, you'll see that the FK and other constraints all point to the correct table, albiet with misleading names which refer to the old name.
at scripting time the current names are extracted from the metadata to produce the scripts from the id to id relationships.
Lowell
September 21, 2015 at 4:53 pm
Assuming you want to create the same Foreign Keys on the "new" table, and other Constraints similarly, there might be several that you need to rename in the event you want to reuse the original Constraint names on the new table (and assuming you want specific names to the constraints rather than random, system generated, ones) whilst the original table is still present (albeit now renamed)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply