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

    Thursday, June 15, 2006 9:41 AM

Answers

  • Try to not verifying existing Data when you create the FK, it solve the problem.
    Tuesday, October 10, 2006 1:07 PM
  • 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.

    Thursday, June 15, 2006 1:30 PM

All replies

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

    Thursday, June 15, 2006 1:30 PM
  • 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.

    Monday, September 25, 2006 12:27 AM
  • do sp_help 'tb' to see what constraint has been put on the tb.

     

    Monday, September 25, 2006 2:42 AM
  • Try to not verifying existing Data when you create the FK, it solve the problem.
    Tuesday, October 10, 2006 1:07 PM
  • Thank goodness for Google and Chilirecords... really saved my day.
    Cheers,
    cat
    Friday, October 20, 2006 5:08 PM
  • "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!

     

    Tuesday, November 07, 2006 5:00 PM
  • 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)


    Monday, November 13, 2006 2:20 PM
  • 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
    /°\
    • Proposed as answer by TDN Wednesday, January 27, 2010 11:02 PM
    Monday, May 28, 2007 9:57 PM
  •  

    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.

    Wednesday, June 27, 2007 1:12 PM
  • 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.

    Thursday, July 26, 2007 3:57 PM
  •  

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

    Wednesday, October 10, 2007 11:20 AM
  • 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 Smile

    Friday, November 02, 2007 8:34 PM
  • 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!
    • Proposed as answer by Davor Bursac Thursday, November 11, 2010 11:01 AM
    Friday, February 08, 2008 9:40 AM
  • 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.

    • Proposed as answer by Ken L. Cooley Tuesday, November 30, 2010 6:17 AM
    Thursday, February 21, 2008 9:04 AM
  • 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. 

    Monday, February 25, 2008 4:00 PM
  •  

    I got the solution from the above messages. It was the orphaned records on the existing which were giving a problem. Thanks a lot!
    Tuesday, April 22, 2008 9:18 PM
  • Chilirecords Thank you very much !! I been trying for about two days reading and web surfing every where. Just in time !!!

     

    Thursday, May 01, 2008 1:36 PM
  • Give me some idea to solve this problem sir....

     

    Thanks & Regards

     

     

     

    Samraj

     

     

    Thursday, July 31, 2008 9:19 AM
  •  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

     

     

    Thursday, July 31, 2008 9:34 AM
  • 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
    • Proposed as answer by Gabru420 Friday, February 13, 2009 12:04 AM
    Wednesday, February 11, 2009 2:13 PM
  • 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
    Friday, February 13, 2009 12:07 AM
  • 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.
    Tuesday, February 17, 2009 2:19 PM
  • 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.
    Friday, April 10, 2009 10:23 PM
  • 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])


    Friday, January 29, 2010 9:30 PM
  • Thank you!
    • Proposed as answer by TaGen Monday, January 24, 2011 2:24 PM
    Tuesday, October 05, 2010 2:36 PM
  • 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

    Tuesday, January 25, 2011 4:58 AM
  • 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:)
    • Proposed as answer by S Kumar Dubey Friday, May 13, 2011 6:33 AM
    Friday, May 13, 2011 6:20 AM
  • I had also this problem, Table / Tables have some Date, which is not suitable to make a foreign Kay. Therefore you must have to delete the records or correct records in Tables first. After correction, SQL Command will execute successfully.

    Aamir Jahangir, Karachi

    Tuesday, May 29, 2012 9:58 AM
  • @Andy Shellamone of the best answer
    • Edited by sn-k Wednesday, October 10, 2012 9:43 AM
    Wednesday, October 10, 2012 9:42 AM
  • Gracias. Thanks

    9 hours 48 minutes ago