The DELETE statement conflicted with the REFERENCE constraint

Question

2.26/5 (7 votes)

See more:

SQL

The DELETE statement conflicted with the REFERENCE constraint "FK_User_History_Tbl_Customer". The conflict occurred in database "KHUB_DB", table "dbo.User_History", column 'UserId'.
The statement has been terminated.

Posted 1-Nov-13 0:45am

sanjaysgh

Updated 18-Sep-22 6:35am

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

v2

Add a Solution

Comments

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 2-Nov-13 7:37am

Why have you rejected my answer? You accepted it yesterday.

Is there any problem? Please feel free to ask.

If you did it by mistake, then please accept it again.

Thanks,
Tadit.

sanjaysgh 2-Nov-13 7:57am

sir i did it by mistake now i accept ur answer again

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 2-Nov-13 8:17am

Thanks a lot. No problem, it happens with many members... :)

4 solutions

Solution 5

The solutions above explain what the problem is but do not explain explicitly how to fix it - which can be done without writing any SQL queries or other code.

The easiest solution is to go to SSMS and open the database diagram

select the relation

Then in its properties select On Delete - cascade

Then everything is done for you by SSMS

Posted 6-Mar-18 17:46pm

jizz

Comments

CHill60 7-Mar-18 3:42am

Really? You reopened 4 year old question for this?
"Cascade delete" means that child entries are deleted when a record is deleted from the Parent table. The situation described by the OP is where they are trying to delete the child row before deleting the rows that depend on it OR before removing the data from the FK column.
Sure, cascade delete would mean only a single step if they deleted the row in the parent table, but it doesn't solve this problem. The accepted answers obviously do though.
Stick to answering new posts where the OP still needs help and make sure you read the question carefully

jizz 8-Mar-18 3:32am

I found this thread because I had the exact same error in an application. The solution I have provided fixed it - so thats all that matters. You must be mistaken sorry. What SSMS implements in the SQL I do not know. But I assure you I had the exact same error & the procedure is correct. I implemented this - no further error & my application works perfectly. So some poor coder may come accross the rambling about the SQL and spend an hour trying to figure it all out. On other sites I have found multiple ramblings about coding the C# to correct the error. All along it is simply corrected in SSMS. Perhaps some people like to look clever when they are afflicted with analysis paralysis & should instead be maximizing the tools available to be productive. Also I dont care if a thread is 1000 years old - if I have a contribution I will make it.

CHill60 8-Mar-18 4:53am

I am not mistaken - or if I am then Microsoft documentation must also be mistaken. Unlikely.
SSMS does not implement anything "in the SQL". Users running programs should not need to have SSMS installed on their kit. All you have demonstrated with your response is that you do not know how to develop applications that use SQL Server databases in a non-development environment.
I'm obviously not the only one as I note your "solution" has been downvoted. My advice still stands

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) · Accepted Answer · 2013-11-01T01:03:00

You are trying to Delete the record from a Table which has a reference in another Table.

Here REFERENCE constraint is "FK_User_History_Tbl_Customer".

Tables are User_History and Tbl_Customer.
UserId column is referenced by Tbl_Customer Table.

When you try to delete a row from User_History Table, it comes to know that the same row has some related row in Tbl_Customer Table.

So, you need to delete from Tbl_Customer Table first and then delete from Table User_History.

If you want to delete a row, then you have to execute the following two queries in sequence.

SQL

DELETE FROM Tbl_Customer
WHERE UserId = [[[UserIdToDelete]]];

DELETE FROM User_History
WHERE UserId = [[[UserIdToDelete]]]

Dharmesh Kanzariya · Accepted Answer · 2013-11-01T00:58:00

Solution 1

hello,

first you have to delete the record from the Foreign Table and then after you have to delete the record from the Primary Key Table(Master Table).

best wishes....

Posted 1-Nov-13 0:58am

Dharmesh Kanzariya

OriginalGriff · Accepted Answer · 2013-11-01T00:59:00

Yes?
The message is pretty explicit: you are trying to delete something which is referenced elsewhere in the database and there is a constraint that you cannot do that to prevent "hanging" references.

Probably, what you are trying to delete is a row that is used as a Foreign key in a different table: "User_history". To remove it, you must first remove the rows that rely upon it.