The ALTER TABLE statement conflicted with the CHECK constraint in SQL Server - SQL Server / TSQL Tutorial Part 89

Scenario:

You are working as SQL Server developer, you are asked to add Check Constraint to one existing table dbo.Employee on FName column and write logic for Check Constraint so it should always accept alphabets.

When you tried to add Check Constraint, you got below error.

Msg 547, Level 16, State 0, Line 19
The ALTER TABLE statement conflicted with the CHECK constraint "Chk_dbo_Employee_FName". 
The conflict occurred in database "YourDatabaseName", table "dbo.Employee", column 'FName'.

Solution:

Let's generate the scenario first for the error. Create sample dbo.Employee table with some sample data.

--Create Table  
use YourDatabaseName
go
Create table dbo.Employee
(
FName VARCHAR(100) Not Null,
LName VARCHAR(100),
StreetAddress VARCHAR(255)
)
--Insert data in sql table
insert into dbo.Employee(FName,LName,StreetAddress)
values ('Aamir','Shahzad','xyz address')
go
insert into dbo.Employee(FName,LName,StreetAddress)
values ('Raza A',Null,'abc address')
go

Now run the alter table statement to add Check Constraint.  Once you will execute this statement you will get above error. as existing data does not qualify for Check Constraint. We have space in first name for 'Raza A' and our Check Constraint says that the data in FName should be always alphabets.

Alter table dbo.Employee
Add Constraint Chk_dbo_Employee_FName
Check (FName not like '%[^a-z]%')

1) First Solution: Correct Existing Data

Fist solution can be, you find the data that does not qualify for Check Constraint and correct that and then add Check Constraint.

2) If business don't want to fix the existing data and want to implement Check Constraint from moving forward, you can create the Check Constraint with Nocheck. By doing that it will not validate existing data against our Check Constraint rule but only apply to new data.

Alter table dbo.Employee with nocheck
Add Constraint Chk_dbo_Employee_FName
Check (FName not like '%[^a-z]%') 

Let's insert couple of records and check if our Constraint is working as expected.

insert into dbo.Employee(FName,LName,StreetAddress)
values ('Test 123',Null,'test address')
go

insert into dbo.Employee(FName,LName,StreetAddress)
values ('Najaf',Null,'test address')
go

The first insert will fail as it does not qualify with our Check Constraint rule. Second record will be inserted successfully. Let's check the data in table now.

How to add Check Constraint to Column with Existing Data in SQL Server 

Video Demo : How to fix error the Alter table statement conflicted with the Check Constraint