How to Add Constraint With No Validation? – Interview Question of the Week #299

Question: How to Add Constraint With No Validation?

Answer: I got this question from one of my clients of Comprehensive Database Performance Health Check who is an Oracle user. I can totally understand where the question is coming from as there is no syntax of NOVALIDATE in SQL Server which is there in the Oracle.

Let us understand the question properly. When the user tries to create a constraint over a table that already contains some value that does not satisfy the constraint it gives an error.

However, there are many business scenarios when you have to enforce the constraint or rule going forward and do not have to correct the data which is already populated in the table. In this scenario, you can easily create a constraint that does not check the previous value in the table.

In Oracle the syntax for the same is NOVALIDATE  and in SQL Server the syntax for the same is NOCHECK.

Now let us understand this with the help of a sample script. This is based on my previous blog SQL SERVER – Identity and Constraint Confusion.

First, create a table and populate it with few records.

CREATE TABLE tblIdentity
(
ID INT IDENTITY(1,1),
Cols VARCHAR(100)
)
GO
INSERT INTO tblIdentity (Cols)
VALUES ('RandomValues')
GO 10

Now let us try to create a constraint with the following script.

ALTER TABLE tblIdentity
ADD CONSTRAINT CHECKID
CHECK (ID > 5)
GO

When I try to add constraint using the script above it gives me the following error.

Msg 547, Level 16, State 0, Line 11
The ALTER TABLE statement conflicted with the CHECK constraint “CHECKID”. The conflict occurred in database “master”, table “dbo.tblIdentity”, column ‘ID’.

Now, this is because the table already contains values which this constraint does not support. In this scenario, you can create a constraint with the keyword WITH NOCHECK and skip the validation. Actually, this syntax works the same as no validation script of the Oracle.

Let us see how we can add the constraint which does not do checking of the previous values in the table.

ALTER TABLE tblIdentity
WITH NOCHECK
ADD CONSTRAINT CHECKID
CHECK (ID > 5)
GO

Now when you run the script above, it will add the constraint and will prevent inserts for the values which violate the constraint.

Reference: Pinal Dave (https://blog.sqlauthority.com)