sql - 在 SQL Server 中临时禁用约束

Question

我想将所有数据从一个数据库复制到另一个数据库，但是我有很多无法轻松插入的外键问题。我想禁用外键，插入数据，然后再次启用它们。最简单的方法是什么？

谢谢，
伊万

Answer 1

-- disable the check_sale constraint in the employee table
ALTER TABLE employee NOCHECK CONSTRAINT check_sale

-- enable the check_sale constraint in the employee table
ALTER TABLE employee WITH CHECK CHECK CONSTRAINT check_sale

如果您的脚本跨越多个表，您可以检索所有约束并禁用所有约束。

USE AdventureWorks;
GO
SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'
GO

http://www.mssqlcity.com/Articles/General/using_constraints.htm