ansaurus

Question

Cascade Delete Query

Answer 1

+1 A:

If you can't add constraints that propagates the delete, you have to write all the necessary deletes yourself:

delete employee where companyid in (select companyid from company c where productid = xxx);
delete company where productid=xxx;
delete product where productid=xxx;

ammoQ 2010-08-27 10:57:40

so do I need to write the 3 queries, can't be done in single one?

Nits 2010-08-27 11:02:54

Nope. But you could write a stored procedure around these 3 queries.

pascal 2010-08-27 11:17:09

I think there is a good reason for that. If you want cascading deletes, make a referential constraint with the "ON DELETE CASCADE" option. But in some cases, you simply would not want that, because the danger of deleting way more than intended is to big. For example, you definitely don't want to delete an invoice item just because someone tries to delete the product that was sold. If there was a "CASCADE" option for the DELETE statement, people would use it without knowing how much damage they cause.

ammoQ 2010-08-27 11:36:34

The downside that I see to this is that nothing forbids another user from adding an employee while query 1 above is running, causing query 2 to bomb out with a foreign key constraint violation. With foreign keys with cascade delete, that won't be a problem, although the deleting session will hang until the other session commits or rolls back. What you should do is use both ON DELETE CASCADE and use the SQL above. (On Delete Cascade is notoriously slow.)

Adam Musch 2010-08-27 21:00:20

Answer 2

A:

Try this option. I don't have environment to test this. I guess with some changes it should work at your end.

DELETE Product,Company,Employee 
FROM 
user LEFT JOIN items ON product.productid = company.productid 
LEFT JOIN orders ON company.productid = product.productid 
WHERE product.productid = [$productid]

Pravin Satav 2010-08-27 11:39:57

ansaurus

tags:

views:

answers:

Cascade Delete Query

related questions