WITHCHECK 添加约束，然后是 CHECK 约束还是 ADD 约束

我查看了 SQLServer2008的 AdventureWorks 示例数据库，并且在它们的创建脚本中看到它们倾向于使用以下内容:

ALTER TABLE [Production].[ProductCostHistory] WITH CHECK ADD
CONSTRAINT [FK_ProductCostHistory_Product_ProductID] FOREIGN KEY([ProductID])
REFERENCES [Production].[Product] ([ProductID])
GO

紧接着是:

ALTER TABLE [Production].[ProductCostHistory] CHECK CONSTRAINT
[FK_ProductCostHistory_Product_ProductID]
GO

我在外键(如此处)、唯一约束和常规 CHECK约束中看到了这一点; DEFAULT约束使用我更熟悉的常规格式，如:

ALTER TABLE [Production].[ProductCostHistory] ADD  CONSTRAINT
[DF_ProductCostHistory_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
GO

如果有的话，第一种方法和第二种方法有什么区别呢？

141447 次浏览
小开
最佳答案

第一种语法是多余的—— WITH CHECK是新约束的默认值，默认情况下也打开了约束。

这种语法是由 SQL 管理工作室在生成 SQL 脚本时生成的——我假设它是某种额外的冗余，可能是为了确保即使表的默认约束行为被更改，也能启用约束。

小开

WITH CHECK确实是默认行为，但是在编码中包含它是一个很好的实践。

另一种行为当然是使用 WITH NOCHECK，所以最好明确地定义您的意图。这通常在使用/修改/切换内联分区时使用。

小开

外键和检查约束具有受信任或不受信任以及启用和禁用的概念。有关 ALTER TABLE的详细信息，请参阅 MSDN 页面。

WITH CHECK默认用于添加新的外键和检查约束，WITH NOCHECK默认用于重新启用禁用的外键和检查约束。意识到差异是很重要的。

话虽如此，实用程序生成的任何明显多余的语句都只是为了安全和/或易于编码。别担心他们。

小开

当表中的现有数据不符合定义的约束并且您不希望它与您正在实现的新约束冲突时，也可以使用 WITH NOCHECK..。

小开

为了演示它是如何工作的

CREATE TABLE T1 (ID INT NOT NULL, SomeVal CHAR(1));
ALTER TABLE T1 ADD CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED (ID);


CREATE TABLE T2 (FKID INT, SomeOtherVal CHAR(2));


INSERT T1 (ID, SomeVal) SELECT 1, 'A';
INSERT T1 (ID, SomeVal) SELECT 2, 'B';


INSERT T2 (FKID, SomeOtherVal) SELECT 1, 'A1';
INSERT T2 (FKID, SomeOtherVal) SELECT 1, 'A2';
INSERT T2 (FKID, SomeOtherVal) SELECT 2, 'B1';
INSERT T2 (FKID, SomeOtherVal) SELECT 2, 'B2';
INSERT T2 (FKID, SomeOtherVal) SELECT 3, 'C1';  --orphan
INSERT T2 (FKID, SomeOtherVal) SELECT 3, 'C2';  --orphan


--Add the FK CONSTRAINT will fail because of existing orphaned records
ALTER TABLE T2 ADD CONSTRAINT FK_T2_T1 FOREIGN KEY (FKID) REFERENCES T1 (ID);   --fails


--Same as ADD above, but explicitly states the intent to CHECK the FK values before creating the CONSTRAINT
ALTER TABLE T2 WITH CHECK ADD CONSTRAINT FK_T2_T1 FOREIGN KEY (FKID) REFERENCES T1 (ID);    --fails


--Add the CONSTRAINT without checking existing values
ALTER TABLE T2 WITH NOCHECK ADD CONSTRAINT FK_T2_T1 FOREIGN KEY (FKID) REFERENCES T1 (ID);  --succeeds
ALTER TABLE T2 CHECK CONSTRAINT FK_T2_T1;   --succeeds since the CONSTRAINT is attributed as NOCHECK


--Attempt to enable CONSTRAINT fails due to orphans
ALTER TABLE T2 WITH CHECK CHECK CONSTRAINT FK_T2_T1;    --fails


--Remove orphans
DELETE FROM T2 WHERE FKID NOT IN (SELECT ID FROM T1);


--Enabling the CONSTRAINT succeeds
ALTER TABLE T2 WITH CHECK CHECK CONSTRAINT FK_T2_T1;    --succeeds; orphans removed


--Clean up
DROP TABLE T2;
DROP TABLE T1;
小开

除了上述关于可信限制的出色评论之外:

select * from sys.foreign_keys where is_not_trusted = 1 ;
select * from sys.check_constraints where is_not_trusted = 1 ;

不受信任的约束(正如其名称所暗示的那样)现在不能被信任为准确地表示表中数据的状态。但是，它可以，但是可以信任，以便在将来检查添加和修改的数据。

此外，查询优化器会忽略不受信任的约束。

启用检查约束和外键约束的代码非常糟糕，“检查”一词有三种含义。

ALTER TABLE [Production].[ProductCostHistory]
WITH CHECK -- This means "Check the existing data in the table".
CHECK CONSTRAINT -- This means "enable the check or foreign key constraint".
[FK_ProductCostHistory_Product_ProductID] -- The name of the check or foreign key constraint, or "ALL".
小开

下面是我编写的一些代码，它们可以帮助我们在数据库中识别和纠正不受信任的 CONSTRINT。它生成代码来修复每个问题。

    ;WITH Untrusted (ConstraintType, ConstraintName, ConstraintTable, ParentTable, IsDisabled, IsNotForReplication, IsNotTrusted, RowIndex) AS
(
SELECT
'Untrusted FOREIGN KEY' AS FKType
, fk.name AS FKName
, OBJECT_NAME( fk.parent_object_id) AS FKTableName
, OBJECT_NAME( fk.referenced_object_id) AS PKTableName
, fk.is_disabled
, fk.is_not_for_replication
, fk.is_not_trusted
, ROW_NUMBER() OVER (ORDER BY OBJECT_NAME( fk.parent_object_id), OBJECT_NAME( fk.referenced_object_id), fk.name) AS RowIndex
FROM
sys.foreign_keys fk
WHERE
is_ms_shipped = 0
AND fk.is_not_trusted = 1


UNION ALL


SELECT
'Untrusted CHECK' AS KType
, cc.name AS CKName
, OBJECT_NAME( cc.parent_object_id) AS CKTableName
, NULL AS ParentTable
, cc.is_disabled
, cc.is_not_for_replication
, cc.is_not_trusted
, ROW_NUMBER() OVER (ORDER BY OBJECT_NAME( cc.parent_object_id), cc.name) AS RowIndex
FROM
sys.check_constraints cc
WHERE
cc.is_ms_shipped = 0
AND cc.is_not_trusted = 1


)
SELECT
u.ConstraintType
, u.ConstraintName
, u.ConstraintTable
, u.ParentTable
, u.IsDisabled
, u.IsNotForReplication
, u.IsNotTrusted
, u.RowIndex
, 'RAISERROR( ''Now CHECKing {%i of %i)--> %s ON TABLE %s'', 0, 1'
+ ', ' + CAST( u.RowIndex AS VARCHAR(64))
+ ', ' + CAST( x.CommandCount AS VARCHAR(64))
+ ', ' + '''' + QUOTENAME( u.ConstraintName) + ''''
+ ', ' + '''' + QUOTENAME( u.ConstraintTable) + ''''
+ ') WITH NOWAIT;'
+ 'ALTER TABLE ' + QUOTENAME( u.ConstraintTable) + ' WITH CHECK CHECK CONSTRAINT ' + QUOTENAME( u.ConstraintName) + ';' AS FIX_SQL
FROM Untrusted u
CROSS APPLY (SELECT COUNT(*) AS CommandCount FROM Untrusted WHERE ConstraintType = u.ConstraintType) x
ORDER BY ConstraintType, ConstraintTable, ParentTable;
小开

恕我直言，感觉它可能是一个 SSMS (反向逻辑)错误; 因为第二个(现有的/重新启用的约束)语句需要显式包含/使用‘ WITHCHECK’，而不是第一个(默认为 new/‘ WITH-CHECK’)。

我想知道他们是否只是将‘ WITHCHECK’子句的生成应用到了错误的 SQL 语句/第一个 T-SQL 语句，而不是第二个——假设他们试图在两种情况下都默认使用检查——同时为一个新的约束或(重新启用)一个现有的约束。

(在我看来似乎是有道理的，因为禁用检查约束的时间越长，理论上破坏/检查约束无效数据同时潜入的可能性就越大。)


提交答案