FK constraints and sp_rename

Question

FK constraints and sp_rename

robert.gerald.taylor

SSChampion

Points: 13133
More actions
May 10, 2017 at 2:50 pm

#323418

Situation:
Assume I've got a [Table1] that is referred to by many FK constraints.
Can I
1) Create a new [Table1_temp]
2) Do an insert from [Table1] into [Table1_temp]
3) Rename (via sp_rename) all indexes, constraints from Table1 to add _old to name
4) Rename Table1 to Table1_old
5) Rename Table1_temp to Table1
Will the FK constraints from all of the tables point to the new table that was renamed to be Table1? I guess, I'm asking if behind the scenes, does SQL Server use a guid or table id number, or is the table name how FK relationships are enforced?
Caveats:
* I know how to do all of steps 1-5
* Specific schema doesn't matter for table (stay off my back Celko)
* I know I can drop and recreate FKs from other tables if this doesn't work.
I was just wondering if I could be tricky and do a switcheroo by using renaming.
Thanks,
Rob

Viewing 3 posts - 1 through 2 (of 2 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

the FK's point to the object_id of that lookup table,and not the name. you can see that is some of the sys views, like select * from sys.foreign_keys; the name is there of the FK, but not the objects they point to...just theirobject that they are tied to (parent_object_id) and what they point to(referenced_object_id)

so you can rename the referenced FK table, the parent table, as well as the FK's themselves, and they still point to the same id, whatever you rename the tables to. the same goes for indexes, default or check constraints, pretty much anything that is in sys.objects or sys.indexes can be renamed.
hope that helps!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

robert.gerald.taylor SSChampion Points: 13133 More actions · Answer 2

Thank you Lowell. That's exactly what I was after.

Thanks,
Rob