why am getting error for this query?

Question

0.00/5 (No votes)

See more:

SQL

delete from Tbl_Sales where C_INVOICE='AP-SIV-VS-111' and c_year=2015

am getting error for this

Msg 547, Level 16, State 0, Procedure Trig_Sales_delete, Line 31
The DELETE statement conflicted with the REFERENCE constraint "FK_Tbl_Sales_Details_Tbl_Sales". The conflict occurred in database "Pulse_VIVO", table "dbo.Tbl_Sales_Details".
The statement has been terminated.

please tell where is the issue?

Posted 6-Oct-15 0:30am

Member 11337367

Updated 6-Oct-15 0:44am

Andy Lanng

v2

Add a Solution

3 solutions

Solution 2

"Tbl_Sales_Details" contains data which refer to table "Tbl_Sales" and for this reference a foreign key is defined...but unfortunatelly (?) without the Option "ON DELETE CASCADE".

Either you redefine the foreign key or you delete the Details first from "Tbl_Sales_Details" and than you can delete in "Tbl_Sales". Attention: this two delete should be done inside a Transaction.

I hop it helps.

Posted 6-Oct-15 0:53am

User 11060979

Solution 3

There is a PrimaryKey and ForeignKey relation between tables, whenever you try to delete from parent table before that you should delete from child record. Then only it's allowing you to delete parent record.

Posted 6-Oct-15 1:45am

Naveen.Sanagasetti

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)

Andy Lanng · Accepted Answer · 2015-10-06T00:50:00

I can tell from the constraint: FK_Tbl_Sales_Details_Tbl_Sales that there are two tables linked together: Tbl_Sales and Tbl_Sales_Details.

The link is called a Foreign Key. There is a field in Tbl_Sales_Details that will refer to the id in Tbl_Sales. This link cannot be broken. Any id in Tbl_Sales_Detailsmust already be in Tbl_Sales. You cannot delete the record because doing so would break the link.

That is what this message means
The DELETE statement conflicted with the REFERENCE constraint "FK_Tbl_Sales_Details_Tbl_Sales"
The rest just tells you where the issue occurred. It even tells you the conflict table name:
... table "dbo.Tbl_Sales_Details"

You need to delete the details before you can delete the sale.

something like:

SQL

delete sd.* from Pulse_VIVO..Tbl_Sales_Details sd
inner join Tbl_Sales s on ds.sales_id = s.id
 where s.C_INVOICE='AP-SIV-VS-111' and s.c_year=2015


delete from Tbl_Sales where C_INVOICE='AP-SIV-VS-111' and c_year=2015

but beware: this might not be the end of the line. There could be a link to Tbl_Sales and Tbl_Sales_Details in Tbl_bill for example. It all depends on your data structure.

Good luck ^_^
Andy