How does sql server determine order of cascade deletes to many tables RRS feed

  • Question

  • If have the following scenario

    • Table A
    • Table B
      • fk to Table A
    • Table C
      • fk to Table A
      • fk to Table B - nullable

    Table A cascades to table b and Table A cascades to Table C.  Table B then restricts to Table C to avoid circular reference.  However, the way our data is configured, the restriction from B to C will never matter when deleting from A if Table C is always cascaded to first and then B. 

    I have done a few tests and this appears to work.  I haven't been able to find any info on this, so is this order something I can always rely on?

    Friday, October 27, 2006 6:17 PM

All replies

  • There is no guarantee which one will get cascaded first. If you want full control, you should consider implementing trigger.

    Friday, October 27, 2006 6:45 PM
  • So this could change on each delete?
    Friday, October 27, 2006 6:57 PM
  • Yes. There is no way to define the delete cascade happens on C before on B. By implementing delete trigger on A, you have full control as to delete from B before deleting from C.

    Friday, October 27, 2006 7:10 PM