blocks|key|1629619|text|第一个语法是冗余的-+WITH+CHECK是新约束的默认语法，并且默认情况下约束也是打开的。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1629620|此语法是由SQL+management+studio在生成sql脚本时生成的--我假设它是某种额外的冗余，可能是为了确保即使表的默认约束行为发生更改，也会启用约束。|1629621|entityMap^0|B|A|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|M|8|@]|D|@]|E|$]]|$1|H|3|-4|5|6|7|N|8|@]|D|@]|E|$]]]|I|$]]

The first syntax is redundant - the <code>WITH CHECK</code> is default for new constraints, and the constraint is turned on by default as well.
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.

blocks|key|1414971|text|WITH+CHECK确实是默认的行为，但是最好在你的代码中包含它。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1414972|当然，另一种行为是使用WITH+NOCHECK，因此显式定义您的意图是很好的。当你使用/修改/切换内联分区时，这通常是用到的。|1414973|entityMap^0|0|A|0|B|C|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|H|3|-4|5|6|7|P|8|@]|D|@]|E|$]]]|I|$]]

<code>WITH CHECK</code> is indeed the default behaviour however it is good practice to include within your coding.

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

blocks|key|1629754|text|Foreign+key和check约束具有受信任或不受信任以及启用和禁用的概念。有关详细信息，请参阅ALTER+TABLE的MSDN页面。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1629755|WITH+CHECK是添加新外键和检查约束的默认值，WITH+NOCHECK是重新启用已禁用的外键和检查约束的默认值。重要的是要意识到其中的区别。|1629756|话虽如此，实用程序生成的任何明显冗余的语句都只是为了安全和/或便于编码。别担心他们。|1629757|entityMap^0|1E|B|0|0|A|Q|C|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@$9|M|A|N|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|O|8|@$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|T|8|@]|D|@]|E|$]]|$1|J|3|-4|5|6|7|U|8|@]|D|@]|E|$]]]|K|$]]

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 <code>ALTER TABLE</code> for full details.

<code>WITH CHECK</code> is the default for adding new foreign key and check constraints, <code>WITH NOCHECK</code> is the default for re-enabling disabled foreign key and check constraints. It's important to be aware of the difference.

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.

blocks|key|1629826|text|当一个表中的现有数据不符合定义的约束，并且您不希望它与您正在实现的新约束冲突时，也可以使用WITH+NOCHECK。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1629827|entityMap^0|19|C|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

<code>WITH NOCHECK</code> is 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...

blocks|key|1629861|text|为了演示它是如何工作的--|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1629862|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;|code-block|syntax|javascript|1629863|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

To demonstrate how this works--

<pre><code>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;
</code></pre>

blocks|key|1415237|text|在上面关于可信约束的优秀评论的基础上：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1415238|select+*+from+sys.foreign_keys+where+is_not_trusted+=+1+;
select+*+from+sys.check_constraints+where+is_not_trusted+=+1+;|code-block|syntax|javascript|1415239|不受信任的约束，顾名思义，现在不能被信任来准确地表示表中数据的状态。但是，它可以，但可以信任它来检查将来添加和修改的数据。|1415240|此外，查询优化器会忽略不受信任的约束。|1415241|启用check约束和外键约束的代码相当糟糕，"check“一词有三种含义。|1415242|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".|1415243|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

Further to the above excellent comments about trusted constraints:

<pre><code>select * from sys.foreign_keys where is_not_trusted = 1 ;
select * from sys.check_constraints where is_not_trusted = 1 ;
</code></pre>

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

<pre><code>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".
</code></pre>

blocks|key|1630008|text|下面是我写的一些代码，用来帮助我们识别和纠正数据库中的不可信约束。它生成修复每个问题的代码。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1630009|++++;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+{%25i+of+%25i)-->+%25s+ON+TABLE+%25s'',+0,+1'+
++++++++%2B+',+'+%2B+CAST(+u.RowIndex+AS+VARCHAR(64))
++++++++%2B+',+'+%2B+CAST(+x.CommandCount+AS+VARCHAR(64))
++++++++%2B+',+'+%2B+''''+%2B+QUOTENAME(+u.ConstraintName)+%2B+''''+
++++++++%2B+',+'+%2B+''''+%2B+QUOTENAME(+u.ConstraintTable)+%2B+''''+
++++++++%2B+')+WITH+NOWAIT;'
++++%2B+'ALTER+TABLE+'+%2B+QUOTENAME(+u.ConstraintTable)+%2B+'+WITH+CHECK+CHECK+CONSTRAINT+'+%2B+QUOTENAME(+u.ConstraintName)+%2B+';'+AS+FIX_SQL
FROM+Untrusted+u
CROSS+APPLY+(SELECT+COUNT(*)+AS+CommandCount+FROM+Untrusted+WHERE+ConstraintType+=+u.ConstraintType)+x
ORDER+BY+ConstraintType,+ConstraintTable,+ParentTable;|code-block|syntax|javascript|1630010|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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.

<pre><code> ;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)--&gt; %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;
</code></pre>

blocks|key|1630098|text|我敢说，这感觉可能是SSMS+(倒置逻辑)错误；因为第二个(现有/重新启用的约束)语句需要显式包含/使用'WITH+CHECK‘，而不是第一个(new/'with-check’默认值)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1630099|我想知道他们是否刚刚将“WITH+check”子句的生成应用于错误的SQL语句/第一个T-SQL语句，而不是第二个语句-假设他们试图在两种情况下都默认使用检查-无论是新的约束还是(重新启用)现有的约束。|1630100|(对我来说似乎是有道理的，因为禁用检查约束的时间越长，在理论上就会增加破坏/检查-约束-无效数据在此期间悄悄进入的可能性。)|1630101|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

Dare I say it, it feels like it might be an SSMS (inverted logic) bug; in that the explicit inclusion/use of the 'WITH CHECK' would be needed for the 2nd (existing/reenabled constraint) statement, not the first (new/'with-check' defaulted).
I'm wondering whether they've just applied the generation of the 'WITH CHECK' clause to the wrong SQL statement / the 1st T-SQL statement rather than the 2nd one - assuming they're trying to default the use of the check for both scenarios - for both a new constraint or (the reenabling of) an existing one.
(Seems to make sense to me, as the longer a check constraint is disabled, the theoretically increased chance that broken/check-constraint-invalid data might have crept-in in the meantime.)

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:

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

followed immediately by :

<pre><code>ALTER TABLE [Production].[ProductCostHistory] CHECK CONSTRAINT 
[FK_ProductCostHistory_Product_ProductID]
GO
</code></pre>

I see this for foreign keys (as here), unique constraints and regular <code>CHECK</code> constraints; <code>DEFAULT</code> constraints use the regular format I am more familiar with such as:

<pre><code>ALTER TABLE [Production].[ProductCostHistory] ADD CONSTRAINT 
[DF_ProductCostHistory_ModifiedDate] DEFAULT (getdate()) FOR [ModifiedDate]
GO
</code></pre>

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

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

SQL Server

数据库

我正在研究SQL Server2008的AdventureWorks示例数据库，我在他们的创建脚本中看到他们倾向于使用以下内容：ALTER TABLE [Production].[ProductCostHistory] WITH CHECK ADD CONSTRAINT [FK_ProductCostHistory_P...

问WITH CHECK ADD CONSTRAINT后跟CHECK CONSTRAINT与ADD CONSTRAINT
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问WITH CHECK ADD CONSTRAINT后跟CHECK CONSTRAINT与ADD CONSTRAINTEN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问WITH CHECK ADD CONSTRAINT后跟CHECK CONSTRAINT与ADD CONSTRAINT
EN