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?

Rootstock

Answer 1

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.

Lantana

It doesn't look like WITH CHECK is actually the default, it's only the default for new data. From msdn.microsoft.com/en-us/library/ms190273.aspx: "If not specified, WITH CHECK is assumed for new constraints, and WITH NOCHECK is assumed for re-enabled constraints." – Outrider

@ZainRizvi: not new data, new constraints. If you disable a constraint with ALTER TABLE foo NOCHECK CONSTRAINT fk_b and then re-enable it with ALTER TABLE foo CHECK CONSTRAINT fk_b it doesn't verify the constraint. ALTER TABLE foo WITH CHECK CHECK CONSTRAINT fk_b is necessary in order to have the data verified. – Delacourt

It was not clear to me reading this initially. The second (redundant) line, is the function to turn on the constraint. Since the constraint is on by default, the second line is redundant. – Kepi

@Chris Hynes, this is obviously a very old answer, but a popular one and on a very popular question. Your answer is a little unclear to me, could I check that I've understood correctly .... There are 2 options listed: A) (containing 2 SQL commands) WITH CHECK ADD CONSTRAINT + CHECK CONSTRAINT. B) (containing 1 SQL command) ADD CONSTRAINT. I think you're asserting that option A) contains 2 redundancies ... the WITH CHECK setting on the first ADD CONSTRAINT command is redundant because it's already literally the default setting on that command. ctd... – Javed

... and the entire CHECK CONSTRAINT command is redundant because it is doing something that is already done by the ADD CONSTRAINT command. Have I understood that correctly? – Javed

@Delacourt looks like you also understand this, so in case Chris isn't around anymore, are you able to confirm that I've gotten this right? – Javed

If my understanding is correct, I'll propose a matching edit to the answer to make it entirely clear for future readers. – Javed

Answer 2

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;

Mantis

Clean up-- DROP TABLE T2; DROP TABLE T1; – Mantis

I added the clean-up code from your comment to your actual answer to assist the fly-by-night copy-and-pasters out there. – Mirella

"Fly-by-night copy-and-pasters" seems a bit negative. I would consider myself one of those stack users (for more positive wording...) "who find these types of detailed examples extremely valuable". – Cyclohexane

Derogatory or not, 'fly by night' feels like it describes me perfectly. – Chrysarobin

Answer 3

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

Inversely

Answer 4

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

Warthman

Answer 5

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.

Cantal

But as mentioned in other comments ONLY the default for a new constraint. – Witkin

Answer 6

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.

Rosinweed

Is is this link you are refering to: msdn.microsoft.com/en-us/library/ms190273.aspx ? Does this mean that we have to do a ALTER TABLE table WITH CHECK CHECK CONSTRAINT ALL instead of doing it for each constraint? – Nonconformist

@HenrikStaunPoulsen: Yes that's the link. There is nothing stopping you enabling each constraint individually, but you do have to say WITH CHECK CHECK CONSTRAINT to get them trusted. – Rosinweed

I tried that; I ran "ALTER TABLE [dfm].[TRATransformError] WITH CHECK CHECK CONSTRAINT [FK_TRATransformError_ETLEvent]". But the FK still has Is_Not_Trusted=1. Then I dropped the FK, and re-created it with "WITH CHECK CHECK", and now I have Is_Not_Trusted=0. At last. Do you know why? Please note that I've always had is_not_for_replication = 0 – Nonconformist

@HenrikStaunPoulsen: I don't know, it's always worked fine for me. I run a query like select * from sys.objects where [type] in ('C', 'F') and (objectproperty([object_id], 'CnstIsDisabled') = 1 or objectproperty([object_id], 'CnstIsNotTrusted') = 1) to find disabled and untrusted constraints. After issuing the appropriate alter table statements as above, those constraints do disappear from the query, so I can see it working. – Rosinweed

@HenrikStaunPoulsen, it is because the not_for_replication flag is set to 1. This makes the constraint as not trusted. SELECT name, create_date, modify_date, is_disabled, is_not_for_replication, is_not_trusted FROM sys.foreign_keys WHERE is_not_trusted = 1 In that case you need to drop and recreate the constraint. I use this to accomplish that gist.github.com/smoothdeveloper/ea48e43aead426248c0f Keep in mind that on delete and on update are not specified in this script and you need to take that into account. – Barrios

Answer 7

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;

Mantis

Answer 8

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

Hunsinger

Answer 9

I have read this thread. Thank you!

So, I also ran the below statement from SSMS:

ALTER TABLE datadictionary.[TBFB-DataProductEntity] ADD CONSTRAINT
[FK_TBFB-DataProductEntity_TBFB-DataCategory] FOREIGN KEY
(
DataCategoryId
) REFERENCES datadictionary.[TBFB-DataCategory]
(
SystemId
) ON UPDATE  NO ACTION 
 ON DELETE  NO ACTION 

GO

But when scripting the DDL of the datadictionary.TBFB-DataProductEntity from SSMS, it has both WITH CHECK ADD CONSTRAINT as well as CHECK CONSTRAINT, see below:

/****** Object:  Table [datadictionary].[TBFB-DataProductEntity]    Script Date: 
4/22/2024 7:40:09 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [datadictionary].[TBFB-DataProductEntity](
[SystemId] [int] NOT NULL,
[DataCategoryId] [int] NULL,
PRIMARY KEY CLUSTERED 
(
[SystemId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [datadictionary].[TBFB-DataProductEntity]  WITH CHECK ADD CONSTRAINT 
[FK_TBFB-DataProductEntity_TBFB-DataCategory] FOREIGN KEY([DataCategoryId])
REFERENCES [datadictionary].[TBFB-DataCategory] ([SystemId])
GO

ALTER TABLE [datadictionary].[TBFB-DataProductEntity] CHECK CONSTRAINT [FK_TBFB- 
DataProductEntity_TBFB-DataCategory]
GO

So it is a SSMS bug, thus we should ignore CHECK CONSTRAINT, correct?

Disepalous

Take the tour to understand how this website works. It is not a forum. – Jihad

If you have a new question, please ask it by clicking the Ask Question button. Include a link to this question if it helps provide context. - From Review – Jihad