WITH CHECK ADD CONSTRAINT followed by CHECK CONSTRAINT vs ADD CONSTRAINT

Question

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   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 CHECK constraints  DEFAULT constraints use the regular format I am more familiar with such as   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

User · Answer

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

User · Answer

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 TABLE for full details   WITH CHECK is the default for adding new foreign key and check constraints  WITH NOCHECK 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

User · Answer

The first syntax is redundant - the WITH CHECK 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

User · Answer

WITH CHECK is indeed the default behaviour however it is good practice to include within your coding   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

User · Answer

WITH NOCHECK 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

User · Answer

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

User · Answer

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

[sql] WITH CHECK ADD CONSTRAINT followed by CHECK CONSTRAINT vs. ADD CONSTRAINT

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to check-constraints