Add constraint without checking existing data

In this post “Add constraint without checking existing data” we are going to learn how we can add a constraint on a column which already has invalid data. We create constraints to automatically enforce the integrity of the database. Using constraints we can define the rules for valid set of values for a given column. Let’s learn how we can instruct SQL Server to ignore check on the existing data during constraint creation on a column.

We have an employee table with columns EmpId, EmpName and ContactNumber. Column ContactNumber holds few invalid values, as we don’t had a constraint on this column. Now, we want to accept only valid values in the contact number column being validated with the custom business logic, but we don’t want to remove or modify the already existing data in the column. We need to put a constraint for coming data and not for existing data.

Below is the script to create the dummy employee table:

CREATE TABLE tbl_Employee
(
 EmpId INT,
 EmpName VARCHAR(100),
 ContactNumber VARCHAR(10)
)

Let’s insert few rows in the employee table with invalid contact numbers:

INSERT INTO tbl_Employee
(EmpId, EmpName, ContactNumber)
VALUES('1', 'Employee 1', 'NA'),
('2', 'Employee 2', '---'),
('3', 'Employee 3', 'ABC')

Add constraint without checking existing data

To enforce an integrity check on the data which accepts only 10 digit numeric values in the column with bypassing this check on the existing data, use WITH NOCHECK clause along with ALTER TABLE statement during constraint creation:

ALTER TABLE tbl_Employee WITH NOCHECK
ADD CONSTRAINT CK_tbl_Employee_ContactNumber CHECK (ISNUMERIC(ContactNumber) = 1 AND LEN(ContactNumber) = 10)

Note: We keep the validation logic simple for demo purpose, in real business scenario we may need to use much strict logic to validate an contact number.

Have a look on the table data after adding the constraint on the column. Use below query to get all data from the table:

SELECT * FROM tbl_Employee

Now, insert a row with invalid contact number in the table and see the output:

INSERT INTO tbl_Employee
(EmpId, EmpName, ContactNumber)
VALUES('4', 'Employee 4', 'Invalid')

Output:

Msg 547, Level 16, State 0, Line 24
The INSERT statement conflicted with the CHECK constraint “CK_tbl_Employee_ContactNumber”. The conflict occurred in database “Test”, table “dbo.tbl_Employee”, column ‘ContactNumber’.
The statement has been terminated.

In above image, we can see that the insert statement has been failed because of invalid contact number. Now, ContactNumber column is not accepting the invalid values, but it allows us to bypass this check on existing data.

You can share your valuable inputs in comments and please keep sharing and rating this post. Also, join us on social media to be update, below are our social presence:

Facebook – http://www.facebook.com/sqlrelease
Google plus – http://plus.google.com/+sqlreleasepage
Twitter – https://twitter.com/ergkranjan

Thanks for the reading.