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.
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.