aaron_martone — 2012-09-18T11:52:19-04:00 — #1
I have a table (app_pages) that has 2 different foreign keys (fk_page_status_eid & fk_page_subdomain_eid), each of which share the same primary key in another table (app_entries).
Both app_pages.fk_page_status_eid and app_pages.fk_page_subdomain_eid allow for NULL values.
Well I can setup a relationship for 1, say between app_pages.fk_page_status_eid and app_entries.entry_id so that ON DELETE, the fk_page_status_eid is set to NULL.
Upon saving the relationship, all is good. Now I attempt to do likewise with the other FK.
I setup the second relationship between app_pages.fk_page_subdomain_eid and app_entries.entry_id so that ON DELETE, the fk_page_subdomain_eid is set to NULL.
Well, when I go to save, I now get an error that states:
Introducing FOREIGN KEY constraint 'FK_app_pages_app_entries1' on table 'app_pages' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint. See previous errors.
This makes no sense to me because an 'ON UPDATE NO ACTION' exists already. In my mind, if someone deletes from the app_entries, it would set NULL in either fk_page_status_eid or fk_page_subdomain_eid accordingly, but that isn't how it is.
r937 — 2012-09-18T12:30:28-04:00 — #2
i've seen this error a few times on forums, but i'm afraid i don't remember if there is a solution
at least the "multiple cascade paths" part of the error message is easy to understand, but why it has to be an error situation is not clear to me at all
workaround: set both foreign keys to ON DELETE NO ACTION, to avoid the error message -- this will prevent deletion if the app_entries row has a child in either table, so you would trap the error in your code when it happens, issue manual deletes for the child tables, then delete the app_entries row again
aaron_martone — 2012-09-18T13:20:05-04:00 — #3
I figure since the relationship is to a primary key that auto-increments, I could not set the FK's "ON UPDATE" to anything other than 'NO ACTION' because the DB won't let me just change a PK value willy nilly (makes sense)
I like the idea of the DB either cutting ties to but leaving (set to NULL) rather that cascading a removal (but I can understand that usefulness as well).
Out of curiosity, what good would a relationship be in where the ON UPDATE and ON DELETE are both set to 'NO ACTION'?
That said, with some extra application coding, it should be easy enough to warn the user, and upon approval, remove/null the children before removing the parent. Something like "This record has a parent relationship to other database objects. Would you like to:
Delete Parent & Set Children to NULL (when applicable)
Delete Parent & Set Children to Defaults (when applicable)
Delete Parent & all Related Child Objects
r937 — 2012-09-18T17:12:33-04:00 — #4
for those relationships where an orphan of a parent that doesn't exist or has assumed a new identity would be illogical
never ask this
aaron_martone — 2012-09-19T17:08:19-04:00 — #5
lol. Forget "Would you like to", use more "You're gonna" Got it!
mittineague — 2014-09-18T22:39:41-04:00 — #6
This topic is now archived. It is frozen and cannot be changed in any way.