SQL Server : two foreign keys pointing to one table. Error: Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths

Question

I'm getting the error:

Msg 1785, Level 16, State 0, Line 238
Introducing FOREIGN KEY constraint 'FK_Studios_Members_HeadId' on table 'Studios' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

Below is a simplified version of the two tables I'm having problems with:

CREATE TABLE [Members] 
(
    [MemberId] int NOT NULL IDENTITY
)

CREATE TABLE [Studios] 
(
    [StudioId] int NOT NULL IDENTITY,
    [HeadId] int,
    [OwnerId] int,

    CONSTRAINT [PK_Studios] PRIMARY KEY ([StudioId]),

    CONSTRAINT [FK_Studios_Members_OwnerId] 
         FOREIGN KEY ([OwnerId]) REFERENCES [Members] ([MemberId]) 
            ON DELETE SET NULL,
    CONSTRAINT [FK_Studios_Members_HeadId] 
         FOREIGN KEY ([HeadId]) REFERENCES [Members] ([MemberId]) 
            ON DELETE SET NULL
)

I found that if I switch the order of the two FK's, it will always error on the second one. I don't see why this will cause a cascading problem since both have the "ON DELETE SET NULL".

This is being generated by EF Core code-first, so I need the relationships and can't just hack in trigger in the backend.

What am I missing?

Did you see [this](http://stackoverflow.com/questions/851625/foreign-key-constraint-may-cause-cycles-or-multiple-cascade-paths)? I don't know if it will help to answer the question but it helped me to understand the general problem :-) — SMM, Dec 01 '16 at 20:05
Well, one of many possible duplicates: http://stackoverflow.com/q/21390621/861716 — Gert Arnold, Dec 01 '16 at 20:25

score 0 · Answer 1 · answered Dec 01 '16 at 20:19

I'm not sure of an answer that will work with what you have there and I'm not sure how set in stone your design is...but as an alternate design that I don't think would have the same issue have you considered a separate middle relationship table?

One benefit of this approach is it allows you to have multiple members at each position.

CREATE TABLE [Members] 
(
    [MemberId] int NOT NULL IDENTITY
)

CREATE TABLE [Studios] 
(
    [StudioId] int NOT NULL IDENTITY,

    CONSTRAINT [PK_Studios] PRIMARY KEY ([StudioId]),

)

--Contains Owner, Head, etc.
CREATE TABLE [Relationships] 
(
    [RelationshipId] int NOT NULL IDENTITY,
    [RelationshipId] nvarchar(20) NOT NULL
)

CREATE TABLE [StudioMemberRelationships]
(
    [StudioMemberRelationshipId] int NOT NULL IDENTITY,
    [StudioId] int NOT NULL,
    [MemberId] int NOT NULL,
    [RelationshipTypeId] int NOT NULL,

    CONSTRAINT [FK_StudioMemberRelationships_StudioId] 
         FOREIGN KEY ([StudioId]) REFERENCES [Studios] ([StudioId]) 
            ON DELETE SET NULL,
    CONSTRAINT [FK_StudioMemberRelationships_MemberId] 
         FOREIGN KEY ([MemberId]) REFERENCES [Members] ([MemberId]) 
            ON DELETE SET NULL,
    CONSTRAINT [FK_StudioMemberRelationships_RelationshipId] 
         FOREIGN KEY ([RelationshipId]) REFERENCES [Relationships] ([RelationshipId]) 
            ON DELETE SET NULL
)

SQL Server : two foreign keys pointing to one table. Error: Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths

1 Answers1