Menu
I have three tables. Product, Company, Employee
ProductId of Product table is foregin key for Company and CompanyId of Company table is foregin key for Employee
So on deleting ProductId from Product table, all the related records in other tables should delete. But I can't touch schema(can't use alter table). How should I write the query in that case..
529
Cascade delete- a relational database term used to describe the process by which child records are automatically deleted when their parent record is deleted- is, indeed, powerful. When used intentionally and correctly, cascade delete allows you to reduce the quantity of SQL statements needed to perform delete actions.
ON DELETE CASCADE constraint is used in MySQL to delete the rows from the child table automatically, when the rows from the parent table are deleted. For example when a student registers in an online learning platform, then all the details of the student are recorded with their unique number/id.
Why sql server cascade delete is bad? sql server cascade delete should not cause an unexpected loss of data. If a delete requires related records to be deleted, and the user needs to know that those records are going to go away, then cascading deletes should not be used.
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;
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]
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
