WITH CHECK | NOCHECK OPTION FOR EXISTING DATA CHECK ENFORCEMENT IS IGNORED

Question

I am using Visual Studio 2012, with a SQL database project and running database analysis against this database and it is raising the following warning: WITH CHECK | NOCHECK OPTION FOR EXISTING DATA CHECK ENFORCEMENT IS IGNORED.

My understanding of the error is that the CHECK and NOCHECK constraints will be ignored for the existing data when the script is run (in each case for me, it is an ALTER TABLE).

My question is thus, why would a check constraint be ignored?

Warning ID number

It seems the warning ID has changed so I am including both below so that it is easily searchable in future.
In Visual Studio 2010 this had the warning ID of: SQL03159
In Visual Studio 2012 this has the warning ID of: SQL70588

Related Information

ALTER TABLE [dbo].[MyTable] CHECK CONSTRAINT [FK_MyTable_SomeCol]

Ardolino

I'm using Visual Studio 2010, and the warning ID I get is SQL70588 for an SSDT project. Is the VS2010 warning ID in your question for a non-SSDT DB project? – Archenemy

Answer 1

It isn't about redundancy, but rather the fact that the project is desired state, or the ideal state of the DDL schema.

When applying the project to an existing database, the process (publish, dacpac deployment, etc) will enforce the check constraints as the delta is applied. The with check and with nocheck are therefore meaningless in the project DDL that defines the table and subsequent constraint(s).

The only place CHECK and NOCHECK are honored are in the pre and post deployment scripts.

EDIT:

If you ever generate a script from a dacpac you will notice things happen in phases.

When it comes to applying new constraints, they are always added WITH NOCHECK. The final phase is applying WITH CHECK to all constraints that need it, AFTER post deployment scripts are run. This allows the maximum amount of customization to occur during the various phases, including the pre and post deployment script phase.

Selfannihilation

Answer 2

In my case, this occurred in a script I imported that had a structure like this:

CREATE TABLE [dbo].[ELMAH_Error]
(
    [ErrorId]     UNIQUEIDENTIFIER NOT NULL,
) 
GO
ALTER TABLE [dbo].[ELMAH_Error] WITH NOCHECK ADD 
    CONSTRAINT [PK_ELMAH_Error] PRIMARY KEY NONCLUSTERED ([ErrorId]) ON [PRIMARY]
GO

When I look at the above code, the WITH NOCHECK looks redundant since the table should have only just been created and therefore be empty. So I suspect this code analysis warning is pointing out this redundancy.

Archenemy

Answer 3

Use the "FOREIGN KEY" statement in the table creation script, do not use the "ALTER TABLE" later to create the foreign key. Example:

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

Hope this helps!

Plumbery