ON DELETE CASCADE doesn't work?

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:
tmp.sql.txt
LVL 32
DrDamnitAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
yes, fk_company_has_address_address1  says to delete rows from company_has_address  if the corresponding parent row is deleted from the address table.

There is no "delete parent when all children are gone" contraint.

So, a trigger to look for parents that will be left child-less  should handle it

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DrDamnitAuthor Commented:
I had pretty much come to the at same conclusion, but it's nice to see a heavy weight like you confirm it. :-) (Did you win the Oracle award? I voted for you...).

I have tried this, but it doesn't seem to work. Must be user / syntax error:

Thoughts?

CREATE DEFINER=`root`@`localhost` TRIGGER `company_has_address_BEFORE_DELETE` BEFORE DELETE ON `company_has_address` FOR EACH ROW BEGIN
DELETE FROM address WHERE address.address_id = company_has_address.address_id; END

Open in new window

DrDamnitAuthor Commented:
OK. I seem to have a (disappointing) update here.

This appears to work:
CREATE DEFINER=`root`@`localhost` TRIGGER `company_has_address_BEFORE_DELETE` BEFORE DELETE ON `company_has_address` FOR EACH ROW 
DELETE FROM address WHERE address_id = old.address_id

Open in new window


The problem is: it doesn't work when a record in the company_has_address table is deleted via the CASCADE function. It only works when the delete is executed directly on the table.

So, in my test environment, I load up 25 companies that have a sum total of 45 addresses over 35 relationships.

When I execute:
DELETE FROM COMPANY WHERE 1;  #SCORCHED EARTH!

Open in new window


All the companies disappear. All the relationships in companys_has_address disappear. All the addresses remain.

But, if I execute
DELETE FROM companys_has_address WHERE 1,

Open in new window

the relationships disappear, the addresses disappear, but the companies remain... all orphaned.

Is there a way to make this work as I expected it to? Or am I back to coding this logic into the database using SQL or into the PHP class that abstracts all these relationships?
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

sdstuberCommented:
Could you put a trigger on companys  that would look for records that would become orphans and then do the delete?


I could write such a thing in Oracle PL/SQL, but I'm afraid my mysql-fu is not so strong.


ps - thanks for the vote!  if you know anyone else please ask them too, the voting continues until Oct 15.
DrDamnitAuthor Commented:
@Sean:

If I have to do it in code, I'd prefer to do it in the PHP class that abstracts all this. Or, I will have a nightly routine run that finds orphaned things, and removes them. The database will not have millions of users or queries. It's very niche. So, we can afford to blow a little CPU time on that.

I would have preferred that cascades cascade all the way to the end, but in order for that to happen, I have to put a foriegn key in the address table that links the company to the address. But that's not valuable because that address table holds all addresses for different entities.

Your feedback was most helpful.
sdstuberCommented:
glad I could help

>>>  I will have a nightly routine run that finds orphaned things

Unless it was essential to be up-to-date all the time, I'd probably go with this route since the companies and addresses aren't directly coupled.
Plus this allows for a little nicer IO operations if you happen to do deletes and then reinsert.  No need to cascade deletes all over for data that would end up as effectively static (or mostly so.)
DrDamnitAuthor Commented:
Bingo. That's exactly what I was thinking. It would be rather expensive to do this in real-time when the fact is... if the company is deleted and no longer exists, the orphaned address is worthless and doesn't show up anywhere else because the relationship is gone.

It can be cleaned up at a later time.
DrDamnitAuthor Commented:
For giggles, I put this trigger on companys, heeding your suggestion:
USE `testdb`;

DELIMITER $$

DROP TRIGGER IF EXISTS testdb.companys_BEFORE_DELETE$$
USE `testdb`$$
CREATE DEFINER = CURRENT_USER TRIGGER `companys_BEFORE_DELETE` BEFORE DELETE ON `companys` FOR EACH ROW
DELETE FROM address 
WHERE
    address_id IN (SELECT 
        address_id
    FROM
        company_has_address
    
    WHERE
        companys_id = OLD.companys_id);
    $$
DELIMITER ;

Open in new window


It seems to miss some addresses if a company has more than one address. It consistently leaves 10 of the 45 behind.

If I figure it out, I'll post here for future searchers.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.