The DELETE statement conflicted with the REFERENCE constraint ""

This is one of the most common errors which occurs while you are deleting a record/records from a table.

"Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "". The conflict occurred in database "", table "", column ''.
The statement has been terminated."

This error says that the Table which you are trying to delete the record from is referenced by some other table. This is not actually a error rather its expected behaviour and this is necessary to have business integrity rules to be followed. Solution to this is First Delete the data from the Table where it is being referenced and then delete the data from this table. But this wont hold good all times. Below is an Illustration.

For Example:

Say you are You run a Grocery Shop and you have a customer.

You have a Main Category say "Food" and then Under Food Sub Categories Canned, Dry, Fresh, Grains, Packed etc.

The customer is buying Food of types Dry and Fresh Every week and you have this under customer records. Now customer stops buying Dry but buys only Fresh.

You want to delete from customer Dry items as he is not buying it anymore. But he is still buying Fresh. So you cannot remove the customer.

Ideal way is put a flag against sub category and then switch the flag to off once its not require anymore.

Still if you want to delete, you can Drop the Foreign Key constraint from Table where this is being referenced and once the data is deleted again add the FK.

#Reference, #DeleteStatement, #constraint, #SQL Server