MySQL Forums
Forum List  »  Newbie

How to use delete cascade when joins are the same table
Posted by: Joshua Savage
Date: December 11, 2010 01:20PM

I have a situation where I have 2 tables - tags and association. I use it to manage relationships between tags.

Tag Table
------
id
phrase

Association Table
---------
tag_id -> references Tag table
parent_id -> also references Tag table
...extra fields for other stuff...

I need the association entries related to a tag to be removed when a tag is deleted. However using ondelete cascade as:

CONSTRAINT `association_parent_id_tag_id` FOREIGN KEY (`parent_id`) REFERENCES `tag` (`id`) ON DELETE CASCADE,
CONSTRAINT `association_tag_id_tag_id` FOREIGN KEY (`tag_id`) REFERENCES `tag` (`id`) ON DELETE CASCADE

does not work - I suppose there is some kind of circular reference issue. I get errors like: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails...

I currently have to delete the associations related to the tag as a separate act - how can I get mysql to manage these delete?

Thanks!

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.