SQL WITH CHECK ADD CONSTRAINT 后跟 CHECK CONSTRAINT vs. ADD CONSTRAINT

WITH CHECK ADD CONSTRAINT followed by CHECK CONSTRAINT vs. ADD CONSTRAINT

提问by Wayne Molina

I'm looking at the AdventureWorks sample database for SQL Server 2008, and I see in their creation scripts that they tend to use the following:

我正在查看 SQL Server 2008 的 AdventureWorks 示例数据库，我在他们的创建脚本中看到他们倾向于使用以下内容：

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

followed immediately by :

紧接着是：

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

I see this for foreign keys (as here), unique constraints and regular CHECKconstraints; DEFAULTconstraints use the regular format I am more familiar with such as:

我在外键（如此处）、唯一约束和常规CHECK约束中看到了这一点；DEFAULT约束使用我更熟悉的常规格式，例如：

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

What is the difference, if any, between doing it the first way versus the second?

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

采纳答案by Chris Hynes

The first syntax is redundant - the WITH CHECK is default for new constraints, and the constraint is turned on by default as well.

第一个语法是多余的 - WITH CHECK 是新约束的默认值，并且默认情况下该约束也是打开的。

This syntax is generated by the SQL management studio when generating sql scripts -- I'm assuming it's some sort of extra redundancy, possibly to ensure the constraint is enabled even if the default constraint behavior for a table is changed.

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

回答by Graeme

To demonstrate how this works--

为了演示这是如何工作的——

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;

回答by Greenstone Walker

Further to the above excellent comments about trusted constraints:

除了上述关于可信约束的优秀评论之外：

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

An untrusted constraint, much as its name suggests, cannot be trusted to accurately represent the state of the data in the table right now. It can, however, but can be trusted to check data added and modified in the future.

一个不受信任的约束，正如它的名字所暗示的那样，不能被信任来准确地表示表中数据的状态。但是，可以信任它来检查将来添加和修改的数据。

Additionally, untrusted constraints are disregarded by the query optimiser.

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

The code to enable check constraints and foreign key constraints is pretty bad, with three meanings of the word "check".

启用检查约束和外键约束的代码很糟糕，“检查”一词的三个含义。

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

回答by noonand

WITH NOCHECKis used as well when one has existing data in a table that doesn't conform to the constraint as defined and you don't want it to run afoul of the new constraint that you're implementing...

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

回答by John Sansom

WITH CHECKis indeed the default behaviour however it is good practice to include within your coding.

WITH CHECK确实是默认行为，但是将其包含在您的编码中是一种很好的做法。

The alternative behaviour is of course to use WITH NOCHECK, so it is good to explicitly define your intentions. This is often used when you are playing with/modifying/switching inline partitions.

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

回答by Christian Hayter

Foreign key and check constraints have the concept of being trusted or untrusted, as well as being enabled and disabled. See the MSDN page for ALTER TABLEfor full details.

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

WITH CHECKis the default for adding new foreign key and check constraints, WITH NOCHECKis the default for re-enabling disabled foreign key and check constraints. It's important to be aware of the difference.

WITH CHECK是添加新外键和检查约束WITH NOCHECK的默认值，是重新启用禁用的外键和检查约束的默认值。了解差异很重要。

Having said that, any apparently redundant statements generated by utilities are simply there for safety and/or ease of coding. Don't worry about them.

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

回答by Graeme

Here is some code I wrote to help us identify and correct untrusted CONSTRAINTs in a DATABASE. It generates the code to fix each issue.

这是我编写的一些代码，用于帮助我们识别和纠正数据库中不受信任的约束。它生成代码来解决每个问题。

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