I have three tables in a MYSQL database: company, address, and company_has_address (a 1:many relationship between a company and its various addresses.
I have the foreign keys set to ON DELETE CASCADE, but when I delete a company, the company is deleted, the corresponding record in company_has_addresses is deleted, but the addresses themselves are still in the address table, completely orphaned.
I think I need to use a trigger (BEFORE DELETE) that deletes the address referenced in the company_has_address table from the address table before it's deleted from the company_has_address table.
How do I make a company deletion cascade all the way through the company_has_addresses table to the address table?
For reference, here are the relevant create statements: