6

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'.

closed as off-topic by Paul White, Max Vernon, Kin Shah, marc_s, Marian Apr 3 '14 at 12:02

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Paul White, Max Vernon, Kin Shah, marc_s, Marian
If this question can be reworded to fit the rules in the help center, please edit the question.

  • Try to See this below link i hope your problem is solved stackoverflow.com/questions/4865873/… – Vinoth _S Apr 2 '14 at 11:51
  • 3
    What does your table look like (CREATE TABLE DDL, as well as constraint DDL), and what was your exact statement that you tried to execute, resulting in the error? – Thomas Stringer Apr 2 '14 at 11:51
9

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'.

  • Make sure first, you have no any such records in your child table and take care of that record otherwise it will conflict the constraint. – JP Chauhan Apr 3 '14 at 11:11

Not the answer you're looking for? Browse other questions tagged or ask your own question.