The DELETE statement conflicted with the REFERENCE constraint

17 February,2020 by Jack Vamvas

Question: In the application error logs there is a message - The DELETE statement conflicted with the REFERENCE constraint FK_xxxxxxxxx

What is causing this error ? How can I troubleshoot?

Answer:  The error is occuring because there is an attempt to delete a parent table data before the referenced child table data is deleted

When a database uses a FOREIGN KEY , its purpose is  to create a link between two tables. The foreign key is one or more columns in the a table referencing another table's PRIMARY KEY.

A  child table is the table hosting the FOREIGN KEYS.A parent table is the table hosting the PRIMARY KEY.

The main idea underlying  the FOREIGN KEY is to maintain referential integrity - via blocking deletion of data being referred elsewhere 

There are 2 main patterns for managing PARENT - CHILD DATA

1)Delete the rows from child table  first, then the rows from the parent table.
2)ON DELETE CASCADE.  but is potentially a dangerous practise . Mainly because the application is disassociating the DELETE logic from other logic that may be encapsulated in queries , stored in ad-hoc queries or stored procedures.     This point is up for debate

 

Read more on foreign key constraints

How to drop a SQL column and find foreign keys with sp_fkeys

List Foreign Key Constraints -MS SQL

How to list constraints of a table (SQL Server DBA)

SQL Server – Optimize delete from table

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on The DELETE statement conflicted with the REFERENCE constraint


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer