On MS SQL Server 2005, I have a table that I want to replace. Many other tables have a Foreign Key reference to this table. Is there a script that I can run to simply roll over all these references from the old table to the new table?
在MS SQL Server 2005上,我有一个我想要替换的表。许多其他表都有对此表的外键引用。是否有一个脚本可以运行以简单地将所有这些引用从旧表转移到新表?
Or am I going to have to go through and specifically drop each of these foreign keys, and write a new key for each one?
或者我是否必须经历并专门删除这些外键中的每一个,并为每个外键写一个新密钥?
2 个解决方案
#1
You are safer dropping and recreating them for the new table.
您更安全地删除并为新表重新创建它们。
#2
Or, can you re-use the same Primary Keys for the new table that were in the Old table you are replacing? Then you don't have to update the FKs at all.
或者,您是否可以为要替换的旧表中的新表重用相同的主键?然后你根本不需要更新FK。
If the Primary Key is auto-incrementing "Identity" column, you can use "Set Identity_Insert On", to allow you to insert new records into the new table's identity column with an explicit value (the pk from the old table). Run Set Identity_Insert Off when you're done and you're off and running.
如果主键是自动递增“标识”列,则可以使用“设置标识_插入”,以允许您使用显式值(旧表中的pk)将新记录插入到新表的标识列中。完成后运行Set Identity_Insert Off并关闭并运行。
#1
You are safer dropping and recreating them for the new table.
您更安全地删除并为新表重新创建它们。
#2
Or, can you re-use the same Primary Keys for the new table that were in the Old table you are replacing? Then you don't have to update the FKs at all.
或者,您是否可以为要替换的旧表中的新表重用相同的主键?然后你根本不需要更新FK。
If the Primary Key is auto-incrementing "Identity" column, you can use "Set Identity_Insert On", to allow you to insert new records into the new table's identity column with an explicit value (the pk from the old table). Run Set Identity_Insert Off when you're done and you're off and running.
如果主键是自动递增“标识”列,则可以使用“设置标识_插入”,以允许您使用显式值(旧表中的pk)将新记录插入到新表的标识列中。完成后运行Set Identity_Insert Off并关闭并运行。