Post reply

Many-to-Many Referential Integrity broken?

  • March 23, 2009 at 3:16 pm

    #129978

    Table MonitoringSite

    - SiteName, varchar(12), PK, not null

    Table Component

    - SiteName, varchar(12), PK, not null, FK to MonitoringSite table w cascade update/delete

    - ComponentID, varchar(3), PK, not null

    Table System

    - SiteName, varchar(12), PK, not null, FK to MonitoringSite table w cascade update/delete

    - SystemID, varchar(3), PK, not null

    Table ComponentSystem

    - SiteName, varchar(12), PK, not null, FK to Component and/or System

    - ComponentID, varchar(3), PK, not null, FK to Component table

    - SystemID, varchar(3), PK, not null, FK to System table

    So MonitoringSite table is at top of the tree with child tables Component and System. ComponentSystem table is a junction table to implement many-to-many between Component and System. I cannot set up declarative cascade update and delete from System table AND Component table to the ComponentSystem table.

    Introducing FOREIGN KEY constraint 'FK_ComponentSystem_System' on table 'ComponentSystem' 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.

    Err does not occur till I try to save the table. Same error occurs if I try to make one do set null rather than cascade on update/delete. I understand that a delete of MonitoringSite row would cause multiple cascade paths though frankly I expected SQL Server to be able to handle this.

    Anybody else have this problem? Advice? I used triggers to get around this ... joy.

  • March 23, 2009 at 3:26 pm

    #964827

    - Multiple cascade path are simply NOT allowed if you want to use CASCADE actions.

    - You must either have a delete/archive script or break the cycle (changing the design).


    * Noel

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply