July 29, 2010 at 9:26 am
I had a table TABLE_A, i am renaming this table to TABLE_B and create another table TABLE_A.
There are 5 other tables which had foreign key references on of my columns of my TABLE_A
The strange thing, I face is all the other tables which had foreign key constraints on TABLE_A has started referring TABLE_B after renaming.
I dont want to happen this,I just want the other tables to refer my TABLE_A. is there a way?
Any suggestions?
Kindest Regards,
Paarthasarathy
Microsoft Certified Technology Specialist
http://paarthasarathyk.blogspot.com
July 29, 2010 at 2:38 pm
Two things you can do.
1. Drop all the FK constraints on the other tables while you do what you want to do with renaming TABLE_A as TABLE_B and creating a new TABLE_A; then re-create the FK constraints again.
2. Do something similar to this:
SELECT *
INTO TABLE_B
FROM TABLE_A
TRUNCATE TABLE_A
insert the records you want to have TABLE_A.
July 30, 2010 at 4:37 am
hi Paarthasarathy
when u rename a table, only the name gets changed but the Object ID remains the same. U can check this in sys.Objects table.
the Foreign keys relate to old table because the Sql system saves the Foreign key and primary key info in its own system tables. If you query 'sys.foreign_keys' u will get Foreign key info and 'sys.key_constraints' for primary key info.
In this table u will find that the Foreign key is created by referencing the Object_id and not the Table Name.
so whatever u change the name of a table the FK will point to the same Object id.
For the solution of your problem. i agree with 'BlackBird'
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply