Click here to Skip to main content
15,178,008 members
Please Sign up or sign in to vote.
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
v2
Comments
   
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
   
Thanks a lot. No problem, it happens with many members... :)

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]]]
   
v2
Comments
Iad90 20-May-15 5:33am
   
What if I want to delete only from the Tbl_Customer and keep the User_History without infecting the REFERENCE CONSTRAINT ? Is there any solution for this ?
   
You can do that in query temporarily. But remember to turn them again after your delete operation.

Refer the answer here - How to DELETE without ON DELETE CASCADE (conflict REFERENCE constraint)
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
   
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
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....
   
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.
   

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900