WITH NOCHECK

If you create a table like this:

CREATE TABLE Examples.BadData ( PositiveValue int NOT NULL );

INSERT INTO Examples.BadData(PositiveValue) VALUES (-1),(-2),(-3),(-4);

Then, you want to use CHECK CONSTRAINT,

ALTER TABLE Examples.BadData ADD CONSTRAINT CHKBadData_PostiveValue CHECK(PositiveValue > 0);

In this case, you can use create the constraint and leave the bad data. This can be done by specifying WITH NOCHECK which bypasses the data check:

ALTER TABLE Examples.BadData WITH NOCHECK ADD CONSTRAINT CHKBadData_PostiveValue CHECK(PositiveValue > 0);

After you fixed data you can do as follows,

ALTER TABLE Examples.BadData WITH CHECK

CHECK CONSTRAINT CHKBadData_PostiveValue;

You can check whether the constraints are disabled or trusted using the follow T-SQL,

SELECT is_not_trusted, is_disabled FROM sys.check_constraints WHERE OBJECT_SCHEMA_NAME(object_id) = 'Examples' and OBJECT_NAME(object_id) = 'CHKBadData_PostiveValue';

---

You can also use WITH NOCHECK for Foreign Constraint. Here is an example,

You must modify the ProductReview Table to meet the following requirements:

✑ The table must reference the ProductID column in the Product table ✑ Existing records in the ProductReview table must not be validated with the Product table. ✑ Deleting records in the Product table must not be allowed if records are referenced by the ProductReview table. ✑ Changes to records in the Product table must propagate to the ProductReview table.

To enable referential integrity for the ProductReview table, you can do as follows,

After table dbo.ProductReview

With NOCHECK

ADD CONSTRAINT FK_ProductReview_Product Foreign Key (ProductID)

Reference Product (ProductID)

On Delete NO ACTION

ON UPDATE CASCADE

#lock