SQL Delete vs. Cascade

Question

I'm creating a contact management system that records a company's contact details, contact person, campaign, submitted proposals by the company, and donation.

One company can have many contact people, can submit many proposals in many campaigns, and can donate for many campaigns. It's not required for a company to have contact person, to submit any proposal and to give any donation. I have successfully made queries using INSERT, UPDATE, and SELECT but not DELETE. Right now I'm using this query to delete a company and all of its related data:

DELETE organizations.*, contactdetails.*, proposalorg.*, donationorg.*
FROM organizations, contactdetails, proposalorg, donationorg 
WHERE idOrg='$id' AND  
contactdetails.company_id=organizations.idOrg AND 
proposalorg.company_id=organizations.idOrg AND 
donationorg.company_id=organizations.idOrg

I know it will not delete the company if there are any errors with this query. I have read about DELETE CASCADE as a better option, but I'm not sure how to do it. If anyone has any idea on how to do it, I would really appreciate it.

score 2 · Accepted Answer · answered Dec 18 '12 at 14:34

To use cascading deletes, you would need to have foreign key constraints between the tables. You would set up the cascading delete rule when defining the foreign key, like so:

ALTER TABLE contactdetails ADD
CONSTRAINT FK_contactdetails_company_id FOREIGN KEY (company_id) 
REFERENCES organizations (idOrg)
ON DELETE CASCADE

This would tell the db that when a row is deleted from the organizations table, if there are any rows in contactdetails that reference it, those should be deleted as well.

As for whether this is a good idea or not - that's a matter of opinion. Personally, I don't like to use them since it makes it too easy to accidentally delete lots of data that maybe you didn't intend to delete. But others will disagree I'm sure.

Thanks, @Eric Petreolje! Thanks for your advice. I'll learn more about other DELETE query next time but this time i'll use DELETE CASCADE. since the system has 'export to excel' function, i dont think it should be a big problem about deleting data. Thanks again! — mella, Dec 18 '12 at 15:58

score 0 · Answer 2 · answered Dec 18 '12 at 14:33

You use "on delete cascade" together with a foreign key constraints. A foreign key constraint is usually placed on foreign ids, these are the ids you use to join over several tables.

When you use foreign key constraints together with cascade, you delete only from the main table and don't use table joins, like in your example.

Can you show the important parts of your table structure definition?

See http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

Yes @Adder, I'm using DELETE CASCADE and I've just tried it and it works! Thank you for spending your time for me. — mella, Dec 18 '12 at 16:03

SQL Delete vs. Cascade

2 Answers2