none
Is there a reason for these WITH NOCHECK / CHECK CONSTRAINT

    Question

  • Hello,

    I'm a little bit puzzled by these:

    ALTER TABLE [dbo].[FactFacture] WITH NOCHECK ADD CONSTRAINT [FK_FactFacture_DimConfigurationDArticle] FOREIGN KEY([codeCompagnie], [codeConfigurationDArticle]) REFERENCES [dbo].[DimConfigurationDArticle] ([codeCompagnie], [codeConfigurationDArticle])

    GO
    ALTER TABLE [dbo].[FactFacture] CHECK CONSTRAINT [FK_FactFacture_DimConfigurationDArticle]
    GO

    All the foreign key in a project are created this way, BEFORE data is actuallt inserted in the tables. I have the following questions:

    1) why create the foreign key using "WITH NOCHECK" and immediately after, use the "CHECK CONTRAINT". As far as i know, the foreign should be created with the contraint enabled.

    2) Does the effect of these combos is different if the constraints are enabled AFTER data is loaded in the table.

    There are over 100 foreign keys in that project and before i proceed with changing everythong with a single ALTER TABLE WITH CHECK, I want to be sure that I'm not misunderstanding some advance concept on foreign keys.

    Regards,

    Eric

    • Edited by arkadia Friday, April 24, 2009 2:01 PM
    Friday, April 24, 2009 1:34 PM

Answers

  • - The "with check/nocheck" is to tell SQL Server to check the rows or not before adding or enabling the constraint.
    - The "check / nocheck" is to enable / disable the constraint.

    You can add or enable the constraint without checking existing rows, but the constraint will be marked as not trusted. If you try to add or enable a constraint checking existing rows, and there is a row breaking the constraint, then you will not be able to add or enable the constraint.

    To disable a constraint it does not matter the use of "with check / nocheck".

    If you are creating a table, then it does not make much sense to split the introduction of a new constraint in two parts, like you show in your post, because there is not row to check. When you use the two statements, one to create the constraint and another to enable it, you are asuring that at least the constraint is being created even if there are rows breaking ti.


    Example:


    USE Northwind;
    GO
    
    CREATE TABLE dbo.t1 (
    c1 INT NOT NULL PRIMARY KEY
    );
    
    CREATE TABLE dbo.t2 (
    c1 INT NOT NULL IDENTITY PRIMARY KEY,
    c2 INT NOT NULL);
    GO
    
    -- this row will break the FK constraint
    INSERT INTO dbo.t2(c2) VALUES(1);
    GO
    
    -- add the constraint without checking existing rows
    -- if we do not use "with nocheck", then the constraint can not be added
    ALTER TABLE dbo.t2 WITH NOCHECK
    ADD CONSTRAINT FK_t2_t1 FOREIGN KEY (c2)
    REFERENCES dbo.t1(c1);
    GO
    
    -- the constraint was added but it is marked as not trusted
    SELECT [name], type_desc, is_disabled, is_not_trusted
    FROM sys.foreign_keys
    WHERE parent_object_id = OBJECT_ID('dbo.t2');
    GO
    
    -- try to mark it as trusted
    -- enabling the constraint and checking existing rows
    -- it will fail is there is a row breaking the constraint
    ALTER TABLE dbo.t2 WITH CHECK CHECK CONSTRAINT FK_t2_t1;
    GO
    
    SELECT [name], type_desc, is_disabled, is_not_trusted
    FROM sys.foreign_keys
    WHERE parent_object_id = OBJECT_ID('dbo.t2');
    GO
    
    -- add a row into t1 to fix
    -- the reference from t2
    INSERT INTO dbo.t1(c1) VALUES(1);
    GO
    
    -- try to mark it as trusted
    -- enabling the constraint and checking existing rows
    ALTER TABLE dbo.t2 WITH CHECK CHECK CONSTRAINT FK_t2_t1;
    GO
    
    SELECT [name], type_desc, is_disabled, is_not_trusted
    FROM sys.foreign_keys
    WHERE parent_object_id = OBJECT_ID('dbo.t2');
    GO
    
    DROP TABLE dbo.t2, dbo.t1;
    GO

    AMB
    • Marked as answer by arkadia Monday, April 27, 2009 1:03 PM
    Saturday, April 25, 2009 2:50 AM
    Moderator

All replies

  • I think it is by design. See the difference in the DDL by running this command

    EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'             ---turn off RI
    GO

    See the DDL for your table

    and run this

    EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'           --- turn on RI
    GO


    and see the DDL of your table now.
    Friday, April 24, 2009 8:02 PM
  • - The "with check/nocheck" is to tell SQL Server to check the rows or not before adding or enabling the constraint.
    - The "check / nocheck" is to enable / disable the constraint.

    You can add or enable the constraint without checking existing rows, but the constraint will be marked as not trusted. If you try to add or enable a constraint checking existing rows, and there is a row breaking the constraint, then you will not be able to add or enable the constraint.

    To disable a constraint it does not matter the use of "with check / nocheck".

    If you are creating a table, then it does not make much sense to split the introduction of a new constraint in two parts, like you show in your post, because there is not row to check. When you use the two statements, one to create the constraint and another to enable it, you are asuring that at least the constraint is being created even if there are rows breaking ti.


    Example:


    USE Northwind;
    GO
    
    CREATE TABLE dbo.t1 (
    c1 INT NOT NULL PRIMARY KEY
    );
    
    CREATE TABLE dbo.t2 (
    c1 INT NOT NULL IDENTITY PRIMARY KEY,
    c2 INT NOT NULL);
    GO
    
    -- this row will break the FK constraint
    INSERT INTO dbo.t2(c2) VALUES(1);
    GO
    
    -- add the constraint without checking existing rows
    -- if we do not use "with nocheck", then the constraint can not be added
    ALTER TABLE dbo.t2 WITH NOCHECK
    ADD CONSTRAINT FK_t2_t1 FOREIGN KEY (c2)
    REFERENCES dbo.t1(c1);
    GO
    
    -- the constraint was added but it is marked as not trusted
    SELECT [name], type_desc, is_disabled, is_not_trusted
    FROM sys.foreign_keys
    WHERE parent_object_id = OBJECT_ID('dbo.t2');
    GO
    
    -- try to mark it as trusted
    -- enabling the constraint and checking existing rows
    -- it will fail is there is a row breaking the constraint
    ALTER TABLE dbo.t2 WITH CHECK CHECK CONSTRAINT FK_t2_t1;
    GO
    
    SELECT [name], type_desc, is_disabled, is_not_trusted
    FROM sys.foreign_keys
    WHERE parent_object_id = OBJECT_ID('dbo.t2');
    GO
    
    -- add a row into t1 to fix
    -- the reference from t2
    INSERT INTO dbo.t1(c1) VALUES(1);
    GO
    
    -- try to mark it as trusted
    -- enabling the constraint and checking existing rows
    ALTER TABLE dbo.t2 WITH CHECK CHECK CONSTRAINT FK_t2_t1;
    GO
    
    SELECT [name], type_desc, is_disabled, is_not_trusted
    FROM sys.foreign_keys
    WHERE parent_object_id = OBJECT_ID('dbo.t2');
    GO
    
    DROP TABLE dbo.t2, dbo.t1;
    GO

    AMB
    • Marked as answer by arkadia Monday, April 27, 2009 1:03 PM
    Saturday, April 25, 2009 2:50 AM
    Moderator
  • Wow! Many thanks for the explanation and complete example! You filled the blanks I had!

    Regards,

    Eric
    Monday, April 27, 2009 1:05 PM