WITH CHECK ADD CONSTRAINT后跟CHECK CONSTRAINT与ADD CONSTRAINT

我正在查看SQL Server 2008的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

如果有的话,第一种方式与第二种方式之间有什么区别？

1> Chris Hynes..：

---

第一种语法是冗余的 - WITH CHECK是新约束的默认值,默认情况下也会启用约束.

这个语法是由SQL管理工作室在生成sql脚本时生成的 - 我假设它是某种额外的冗余,可能确保即使表的默认约束行为发生更改,也会启用约束.

---

它看起来不像WITH CHECK实际上是默认值,它只是新数据的默认值.来自http://msdn.microsoft.com/en-us/library/ms190273.aspx:"如果未指定,则假定WITH CHECK用于新约束,并假定WITH NOCHECK用于重新启用的约束."

---

@ZainRizvi:不是新数据,新约束.如果使用`ALTER TABLE foo NOCHECK CONSTRAINT fk_b`禁用约束,然后使用`ALTER TABLE foo CHECK CONSTRAINT fk_b`重新启用它,则不会验证约束.`ALTER TABLE foo WITH CHECK CHECK CONSTRAINT fk_b`是必要的,以便验证数据.

---

最初阅读此书对我来说还不清楚。第二行（冗余）是打开约束的功能。由于约束默认情况下处于启用状态，因此第二行是多余的。

2> Graeme..：

---

为了证明这是如何工作的 -

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;

---

"夜间复制和粘贴"似乎有点消极.我认为自己是那些堆栈用户之一(更积极的措辞......)"谁发现这些类型的详细示例非常有价值".

---

我将评论中的清理代码添加到您的实际答案中,以帮助那些夜间复制和粘贴.

---

清理 - "DROP TABLE T2; DROP TABLE T1;`

---

贬低与否,'夜间飞行'感觉它完美地描述了我.

3> Greenstone W..：

---

关于可信约束的上述优秀评论:

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".

4> noonand..：

---

WITH NOCHECK 当一个表中的现有数据不符合定义的约束并且您不希望它与您正在实现的新约束相冲突时,也会使用它...

5> John Sansom..：

---

WITH CHECK 确实是默认行为,但在您的编码中包含这是一个好习惯.

替代行为当然是使用WITH NOCHECK,因此明确定义您的意图是很好的.这通常在您使用/修改/切换内联分区时使用.

6> Christian Ha..：

---

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

WITH CHECK是添加新外键和检查约束WITH NOCHECK的默认值,是重新启用禁用外键和检查约束的默认值.重要的是要意识到差异.

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

---

@HenrikStaunPoulsen,这是因为not_for_replication标志设置为1.这使得约束不受信任.SELECT name,create_date,modify_date,is_disabled,is_not_for_replication,is_not_trusted FROM sys.foreign_keys WHERE is_not_trusted = 1在这种情况下,您需要删除并重新创建约束.我使用它来完成https://gist.github.com/smoothdeveloper/ea48e43aead426248c0f请记住,在此脚本中未指定删除和更新,您需要考虑到这一点.

7> Graeme..：

---

以下是我编写的一些代码,用于帮助我们识别和纠正数据库中不受信任的CONSTRAINT.它会生成修复每个问题的代码.

    ;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;