I am not sure if this is necessarily a simple question, but I'm somewhat new to SQL so I thought maybe there's an obvious answer I just don't know about.

The problem is that I have one "master" table, and a child table that has two foreign key references back to that master table. Both of these foreign key constraints are marked as "on delete cascade" with the intention that should a row from the master table be deleted, any rows that reference that object in EITHER foreign key field should be deleted.

I am wondering why this causes a cycle. It seems logical enough to me, it just involves two passes of the table, one for each affected column.


Re: Cyclical reference problem with 2 "on delete cascades" pointing to same table.

Arnie Rowland

There can be only ONE column in a PK-FK link. If your design requires two columns in the child to refer to the Parent, something is amiss and should be revisited. A child can have only ONE Parent. (I know, it's closer to the 'real world' model than we would prefer, but ... ) If two columns in the Child refer to the same row in the Parent, again, there is something specious in the design.

If the row is deleted in the Parent table, it really doesn't matter what column is linked to the Child table, with CASCADE DELETE, rows in the Child table with a PK_FK link will be deleted.