The ALTER TABLE statement conflicted with the FOREIGN KEY constraint

Question

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "fk_Allergy". The conflict occurred in database "MSCPROJECT", table "dbo.Severity", column 'SeverityCode'.

Answer 1

You did not ask a question.  But, I suppose you want to know what this error means.  It means that you are trying to alter the table in a way that violates referential integrity.  If you really need to change the table in a way that does that, you will need to drop the foreign key relationship before you alter the table. 

What the database is doing here is enforcing referential integrity so that you don't get orphaned records as a result of your alteration.  So, be careful and make sure the alteration is something that must occur and make sure to evaluate how the related tables and the data in them will be impacted by your alteration.

Answer 2

Actually, I have the same problem (SQL Express/2005).  I am getting the error trying to create the same constraint that the error is complaining about.  The table previously had no constraints, so it appears as if the new constraint is somehow interfering with itself at some point in the process.

Looks like a bug to me.  If there is a resolution, or a workaround, I would like to know.

Answer 3

do sp_help 'tb' to see what constraint has been put on the tb.

 

Answer 4

Try to not verifying existing Data when you create the FK, it solve the problem.

Answer 5

Thank goodness for Google and Chilirecords... really saved my day.
Cheers,
cat

Answer 6

"Thank goodness for Google and Chilirecords... really saved my day."

x 2

It's such a simple error to run into during development but makes no sense at the time you are working on it.  Just remember to clear out the orphaned records you ignored when you go live!

 

Answer 7

I had to re-read everyone's answer to the issue 3 times before I understood what the solution was.

So for the slow people like myself here's the answer to the issue:

If you're getting this error and you don't have any other relationships and you're getting an error on the constraint that you're adding then the constraint is ALREADY being broken.

That is if you MUST have ONLY the Primary key's on your primary table and the foreign table has entries that are NOT in the primary then you're already breaking your constraint and you should fix that first, (or like one of the other people said, just choose to ignore the current entries and only apply the constraint for future data)

Answer 8

I think that the solution deals with using of WITH NOCHECK key word in order to add a constraint on a table that already have values.

Rakoun
/°\

Answer 9

 

Just to clarify how to solve this in SQL Server 2005....To carry on from what theDamian said.....

 

If you in diagram view on you Database. Right click the table that is being referenced by the foreign key and click References, then click on the reference which is testing your patience. Then all you have to do in the (GENERAL) tab is switch the "Check Existing Data On Creation Or Re-Enabling" to  NO.....Then BANG!!!!!

 

No more probs :-)

 

Enjoy.

Answer 10

I had the same problem... After reading htese message, this is what worked for me...

 

I'm trying to add the constraint on activity_template_id in tbl_work_flow.  It references activity_template_id in tbl_activity_templates.

 

I did an update and changed any records in tbl_work_flow without a matching record in tbl_activity_templates to null.  Then I was able to add the constraint...

 

update tbl_work_flow

set activity_template_id = null

where activity_template_id not in (select activity_template_id from tbl_activity_templates)

 

Hope that helps someone.

Answer 11

 

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "fk_Allergy". The conflict occurred in database "MSCPROJECT", table "dbo.Severity", column 'SeverityCode'.

Answer 12

 

Answer 13

Rakorun was right. I had the same problem. I tried to add constraint to the table with data in it. When i deleted the data i could add foreign key

Answer 14

I had the same problem so tried one of the solutions above about checking existing records but couldn't get this to work either. Eventually I realised that I had a few test records in one of the tables. So to fix this issue, make sure that any tables that you are adding foreign key constraints to are empty.

Job done!

Answer 15

In plain English, this error means that some of the records in your foreign key table do not have a corresponding record in the primary key table.  If you're getting this error and just turn "check existing data" off, then it seems a bit pointless to put a foreign key constraint on, as some of your data is already incorrect.

 

To find which rows are offending the constraint, run:

 

SELECT * FROM <foreign_key_table> WHERE <foreign_key_column> NOT IN (SELECT <primary_key_column> FROM <primary_key_table>);

 

I found the 1 record in 3,000-odd that was stopping me creating my foreign key constraint.

Answer 16

Same here.  After reading posts I checked and found that the row I added to the tbl with fk did not have a key value in the referenced table.  Blew away the offending garbage row and all was good.  Too bad the description for the error raised is so unhelpful.  Such is SQL. 

Answer 17

 

I got the solution from the above messages. It was the orphaned records on the existing which were giving a problem. Thanks a lot!

Answer 18

Chilirecords Thank you very much !! I been trying for about two days reading and web surfing every where. Just in time !!!

 

Answer 19

Give me some idea to solve this problem sir....

 

Thanks & Regards

 

 

 

Samraj

 

 

Answer 20

samrajn wrote:

Give me some idea to solve this problem sir....   [showing the error"      "Msg 547, Level 16, State 0, Line 1 The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_CUSTOMERDETAIL_ORDERNO". The conflict occurred in database "customertable", table "dbo.customerheader", column 'orderno'."]       Thanks & Regards     Samraj

Answer 21

Hey you just need to check wether your client table has got the exact values that are there in your master table

these values are not matching and that is why you are getting this error

Answer 22

The data already present in the tables, does not match the new constraint. Hence the error.

Edit the data in the table, like make sure that all the records in the primary key table are present in the foreign key table. This should work

Answer 23

I had the same error when i created a database diagram, made a connection (become relational) and then save it. It would say the above message. How to cure it, if you have not figured out yet?

The error is misleading. What actually is, is that the two column that are relating to have some mismatched data for example one column has an entry 5 which the other column does not have and then the other colums has a data 4 which the first column does not have. To tie such a relationship, obviously there will be a mismatch of data and connection can not be save because of this. This actually happens if you follow a tutorial eg and you mistype some of the values. To examplain it more

Table A                      Table B
-------------                 ----------------
ID1 First Last              CustID ID1 ID2                    
4                                              6
5                                              7
7

Here if you tie ID1 of tableA to TableB, there is a mismatch of data and therefor you will get this error which is misleading. To correct the error simply change 6 to a valid value like 4 or 5 to Fix the problem.

Answer 24

Thank you for this!  LOL  I was confused until I read your post. 

This was definitely a d'oh! moment for me.  Glad I can finally resolve it though.

Answer 25

It may very well be a problem with the foreign key table containing data not found in the primary key table. You may have rows with no parent in the table your are referencing.

Try this:

   SELECT * from [ForeignKeyTable) WHERE [fieldname] NOT IN (SELECT [fieldname FROM [PrimaryKeyTable])

Answer 26

Thank you!

Answer 27

Try as below:

Delete all the data from the table that you want to use as a reference table. And then after try to use that table as foreignkey

Answer 28

Hi Guys,

Problem:   The ALTER TABLE statement conflicted with the FOREIGN KEY constraint.

This issue is happening becuase Table is having data and you are trying to create Contraint with (WITH CHECK, CHECK CONSTRAINT). If table is having data in that case you have to create contraint with (WITH NOCHECK, NOCHECK CONSTRAINT) and If table is Empty in that case you can go with option (WITH CHECK, CHECK CONSTRAINT).

 Here is the solution of the problem:

If you generate a Create Contraint Script with Table which is having data, It will give in below formate (WITH NOCHECK, NOCHECK CONSTRAINT ).

(How to Take script-> Go SQL Server Management Studio->Table->Expand it-> Keys-> Right Click on the Key for which you want to generate create or Drop Script->Script Key As Create or Drop)

Run the below Script (WITH NOCHECK, NOCHECK CONSTRAINT ) If TABLE1 or TABLE2 is having Data

ALTER TABLE [SCH].[TABLE1]  WITH NOCHECK ADD  CONSTRAINT [FK_EMP] FOREIGN KEY([EMP_ID], [DEP_ID])
REFERENCES [SCH].[TABLE2] ([EMP_ID], [DEP_ID])
ON DELETE CASCADE
GO
ALTER TABLE [SCH].[TABLE1] NOCHECK CONSTRAINT [FK_EMP]

 

If you generate a Create Contraint Script with Table which is having  no data, It will give in below formate (WITH CHECK, CHECK CONSTRAINT ).

Run the below Script (WITH CHECK, CHECK CONSTRAINT) If TABLE1 and TABLE2 are empty

ALTER TABLE [SCH].[TABLE1]  WITH CHECK ADD  CONSTRAINT [FK_EMP] FOREIGN KEY([EMP_ID], [DEP_ID])
REFERENCES [SCH].[TABLE2] ([EMP_ID], [DEP_ID])
ON DELETE CASCADE
GO
ALTER TABLE [SCH].[TABLE1] CHECK CONSTRAINT [FK_EMP]

---

Thanks Shiven:)