Foreign key constraint error with delete

Question

_{Wednesday, May 22, 2013 12:46 PM}

error message:
(i changed the names slightly)

Message
Executed as user: ***. The DELETE statement conflicted with the REFERENCE constraint "fk_child_table_detail_id". The conflict occurred in database "ABC", table "dbo.child_table_detail", column 'parent_table_master_id'. [SQLSTATE 23000] (Error 547)  The statement has been terminated. [SQLSTATE 01000] (Error 3621).  The step failed.

So I have this 3rd party system process that involves several procedures.  These procedures are responsible for "archiving" data from several tables, and in a specific sequence.  The detail tables are archived first, then the main table.  Not sure why now we are getting this error.

Its obvious that the constraint is preventing the delete on the table in question.  However, its the child table that is giving the error, not the main table.

I DO NOT want to remove the constraints, as Ive seen mentioned before elsewhere online.

Any suggestions?

All replies (4)

_{Wednesday, May 22, 2013 2:40 PM ✅Answered}

Sorry, but after looking into it a bit further, there was yet another detail table that had foreign key references to the child table.

---

_{Wednesday, May 22, 2013 1:09 PM | 1 vote}

There is Foreign Key refrence to your table  table "dbo.child_table_detail first you have to drop the contraint..then you can delete the row from child_table..u got this error coz RDBMS has this policy of maintaining the integrity of data.

Disable the foreign key constraint, then delete the row that is giving you this error

Read below article first

http://msdn.microsoft.com/en-us/library/ms175041.aspx

Soldier..Sir we are surrounded from all sides by enemy.. Major: Good, we can attack in any direction Thats attitude.. Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

---

_{Wednesday, May 22, 2013 1:11 PM}

No, the foreign key reference is to the parent table

---

_{Wednesday, May 22, 2013 1:16 PM}

There is constraint and you need to find out...thas why you are not able to delete..e 

I think there is PK FK reference in same table between col ...

it wud be better if you post the query r give info about tables in picture

Soldier..Sir we are surrounded from all sides by enemy.. Major: Good, we can attack in any direction Thats attitude.. Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers