SQLServer删除/重建/禁用/启用外键约束

SQLServer从一个数据库导数到另一个库的时候，如果目标库有外键约束，可能会导致导数失败。这时，可以将外键先删除或禁用，等导数完成后再重建或启用外键。

禁用、启用外键：

[sql] view plain copy

1. DECLARE @nocheckSql NVARCHAR(max)--禁用外键约束的sql
3. SET @nocheckSql =??
4. (SELECT 'alter table dbo.['+b.name+'] nocheck constraint ['+a.name+'];' from sysobjects a,sysobjects b where a.xtype='f' and a.parent_obj=b.id and b.xtype='u' for xml PATH('')?
5. )?
6. print @nocheckSql?
7. --execute sp_executesql @nocheckSql
9. DECLARE @checkSql NVARCHAR(max)--启用外键约束的sql
11. SET @checkSql =??
12. (SELECT 'alter table dbo.['+b.name+'] check constraint ['+a.name+'];' from sysobjects a,sysobjects b where a.xtype='f' and a.parent_obj=b.id and b.xtype='u' for xml PATH('')?
13. )?
14. print @checkSql?
15. --execute sp_executesql @checkSql

删除、重建外键：

[sql] view plain copy

1. declare @createSql nvarchar(max)?
2. declare @delSql nvarchar(max)?
3. Set @createSql = (?
4. select 'ALTER TABLE ['+OBJECT_NAME(k.parent_object_id)+'] ADD CONSTRAINT ['+k.name+'] FOREIGN KEY (['+COL_NAME(k.parent_object_id,c.parent_column_id)+']) REFERENCES ['+OBJECT_NAME(k.referenced_object_id)+'](['+COL_NAME(k.referenced_object_id,key_index_id)+'])'+case k.delete_referential_action when 0 then '' when 1 then ' ON DELETE CASCADE ' when 2 then ' ON DELETE SET NULL ' when 3 then ' ON DELETE SET DEFAULT ' END+ case k.update_referential_action when 0 then '' when 1 then ' ON UPDATE CASCADE ' when 2 then ' ON UPDATE SET NULL ' when 3 then ' ON UPDATE SET DEFAULT' end +';' from sys.foreign_keys k,sys.foreign_key_columns c where c.constraint_object_id=k.object_id for xml path('')?
5. )?
6. print @createSql--重建外键约束的语句
8. set @delSql = (?
9. select 'alter table ['+O.name+'] drop constraint ['+F.name+'];' from sysobjects O,sys.foreign_keys F where F.parent_object_id=O.id for xml path('')?
10. )?
11. print @delSql --删除外键约束的语句
13. --exec sp_executesql @delSql
14. --exec sp_executesql @createSql

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!