1

I'm having problems adding a cascade delete onto a foreign key in SQL Server. Table A has three columns. Column 1 and 2 in Table A are foreign key look ups to the same column in Table B. I want a delete of a row in Table B to cascade a delete on a row on Table A based on these foreign keys.

The other column in Table A has a foreign key lookup to table C. If a row in table C is deleted then I want the corresponding cell to be set to null in Table A.

When I add in these constraints I am thrown the error:

Introducing FOREIGN KEY constraint 'FK_RDU_TODELIVERABLEUNITREF' on table 'RelatedDeliverableUnit' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

I am a little stuck with this, Oracle seems perfectly happy with this logic. I am adding in these constraints using Liquibase. I think the error is down to my logic and not syntax but for completeness here is the liquidbase script that manages the foreign keys:

    <addForeignKeyConstraint constraintName="FK_RDU_FROMDELIVERABLEUNITREF" baseTableName="relatedDeliverableUnit"
                             baseColumnNames="FROMDELIVERABLEUNITREF" referencedTableName="DELIVERABLEUNIT" referencedColumnNames="DELIVERABLEUNITREF" onDelete="CASCADE"/>

    <addForeignKeyConstraint constraintName="FK_RDU_TODELIVERABLEUNITREF" baseTableName="relatedDeliverableUnit"
                             baseColumnNames="TODELIVERABLEUNITREF" referencedTableName="DELIVERABLEUNIT" referencedColumnNames="DELIVERABLEUNITREF" onDelete="CASCADE"/>                        

    <addForeignKeyConstraint constraintName="FK_RDU_RELATIONSHIPREF"
                             baseTableName="relatedDeliverableUnit" baseColumnNames="RELATIONSHIPREF" referencedTableName="RELATIONSHIPTYPES" referencedColumnNames="RELATIONSHIPREF" onDelete="SET NULL"/>

Thanks in advance for any help

Fraser
  • 980
  • 4
  • 13
  • 31

1 Answers1

2

I can't find corresponding documentation for later versions, but the SQL Server 2000 BOL addresses this issue:

The series of cascading referential actions triggered by a single DELETE or UPDATE must form a tree containing no circular references. No table can appear more than once in the list of all cascading referential actions that result from the DELETE or UPDATE. The tree of cascading referential actions must not have more than one path to any given table. Any branch of the tree is terminated when it encounters a table for which NO ACTION has been specified or is the default.

And later versions haven't changed this. You're falling foul of this:

The tree of cascading referential actions must not have more than one path to any given table

The only way I know of to accomplish this is to implement one of the cascades between B and A using an INSTEAD OF trigger, rather than using ON DELETE....

The relation between tables A and C shouldn't be impacted by any of this.

(2008 BOL)

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • Thanks for the advice. I also wondered about adding this is a single foreign key with two columns looking up the same referenced column but I'm not sure that's allowed either. I'll have to look at triggers I guess! – Fraser Oct 08 '12 at 10:04