How to solve the error Msg 547, Level 16, State 0, Line 1 Please suggest me how to solve the error How to solve the error 


  Msg 547, Level 16, State 0, Line 1
  The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "MyForeignKeyName". The conflict occurred in database "MyDatabase", table "dbo.MyTable", column 'IMyColumn'.
If you are trying to make foreign key and your child table already have some data that does not match with your parent table, you will get this error message.

For Example:

CREATE TABLE MyParent(Job_id int PRIMARY KEY,Job_Name Varchar(50));

INSERT INTO MyParent Values(1,'CEO');
INSERT INTO MyParent Values(2,'Business Manager');
INSERT INTO MyParent Values(3,'DBA');

CREATE TABLE MyChild(Emp_id int,Emp_Name Varchar(50),Job_Id int)

INSERT INTO MyChild Values(1,'Lavanya',1);
INSERT INTO MyChild Values(1,'JP Chauhan',3);
INSERT INTO MyChild Values(1,'Rakesh',5);


See the last record of MyChild table that has value in Job_Id is 5, which is not in MyParent table.

If you try to make foreign key relation like:

ALTER TABLE MyChild
ADD CONSTRAINT fk_My_foreignKey FOREIGN KEY(Job_Id)REFERENCES MyParent(Job_Id);


The error message you will get:

Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "fk_My_foreignKey". The conflict occurred in database "mydatabase", table "dbo.MyParent", column 'Job_id'.