How to prevent error: Introducing FOREIGN KEY constraint on table may cause cycles or multiple cascade paths

Question

I'm trying to create the correct database structure for the following situation:

My database:

Has multiple Accounts
An Account can be member of multiple Organizations => OrganizationMember
An Organization has multiple Sections
An OrganizationMember can be member of multiple Sections => SectionMember

My purpose for cascading deletes:

When I delete a Section, I want a cascade delete on all connected SectionMembers
When I delete an OrganizationMember, I want a cascade delete on all connected SectionMembers
When I delete an Organization, I want a cascade delete on all connected OrganizationMembers and Sections (including its SectionMembers)
When I delete an Account, I want a cascade delete on all connected OrganizationMembers (including its SectionMembers)

I tried to create the tables like this:

Account { ID, account_fields }
Organization { ID, organization_fields }
Section { ID, OrganizationID, section_fields }
OrganizationMember {ID, AccountID, OrganizationID, organizationMember_fields }
SectionMember {ID, SectionID, OrganizationMemberID, sectionMember_fields }

Now when I create the tables I get the error:

Introducing FOREIGN KEY constraint 'FK_SectionMembers_Sections_SectionId' on table 'SectionMembers' may cause cycles or multiple cascade paths.

I found this answer: https://stackoverflow.com/a/3548225. This seems to be my case, but the suggested solution won't solve my purpose for cascading deletes.

Question: Is there a way to change the table structure which will solve my purpose for cascading deletes?

SQL generated by EF Core:

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (7ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE [Accounts] (
          [Id] int NOT NULL IDENTITY,
          [IdentityObjectId] nvarchar(40) NOT NULL,
          [DefaultSectionName] nvarchar(max) NULL,
          [Email] nvarchar(80) NOT NULL,
          [FirstName] nvarchar(60) NULL,
          [IsBlocked] bit NOT NULL,
          [LastName] nvarchar(80) NULL,
          [Prefix] nvarchar(16) NULL,
          CONSTRAINT [PK_Accounts] PRIMARY KEY ([Id])
      );
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (6ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE [Organizations] (
          [Id] int NOT NULL IDENTITY,
          [Name] nvarchar(36) NOT NULL,
          CONSTRAINT [PK_Organizations] PRIMARY KEY ([Id])
      );
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (8ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE [OrganizationMembers] (
          [Id] int NOT NULL IDENTITY,
          [AccountId] int NOT NULL,
          [IsBlocked] bit NOT NULL,
          [IsNew] bit NOT NULL,
          [OrganizationId] int NOT NULL,
          [UserName] nvarchar(24) NOT NULL,
          CONSTRAINT [PK_OrganizationMembers] PRIMARY KEY ([Id]),
          CONSTRAINT [FK_OrganizationMembers_Accounts_AccountId] FOREIGN KEY ([AccountId]) REFERENCES [Accounts] ([Id]) ON DELETE CASCADE,
          CONSTRAINT [FK_OrganizationMembers_Organizations_OrganizationId] FOREIGN KEY ([OrganizationId]) REFERENCES [Organizations] ([Id]) ON DELETE CASCADE
      );
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (7ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE [Sections] (
          [Id] int NOT NULL IDENTITY,
          [IsLocked] bit NOT NULL,
          [Name] nvarchar(48) NOT NULL,
          [OrganizationId] int NOT NULL,
          CONSTRAINT [PK_Sections] PRIMARY KEY ([Id]),
          CONSTRAINT [FK_Sections_Organizations_OrganizationId] FOREIGN KEY ([OrganizationId]) REFERENCES [Organizations] ([Id]) ON DELETE CASCADE
      );
fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (35ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE [SectionMembers] (
          [Id] int NOT NULL IDENTITY,
          [AccountLevel] tinyint NOT NULL,
          [IsBlocked] bit NOT NULL,
          [IsNew] bit NOT NULL,
          [OrganizationMemberId] int NOT NULL,
          [SectionId] int NOT NULL,
          CONSTRAINT [PK_SectionMembers] PRIMARY KEY ([Id]),
          CONSTRAINT [FK_SectionMembers_OrganizationMembers_OrganizationMemberId] FOREIGN KEY ([OrganizationMemberId]) REFERENCES [OrganizationMembers] ([Id]) ON DELETE CASCADE,
          CONSTRAINT [FK_SectionMembers_Sections_SectionId] FOREIGN KEY ([SectionId]) REFERENCES [Sections] ([Id]) ON DELETE CASCADE
      );

Error: Introducing FOREIGN KEY constraint 'FK_SectionMembers_Sections_SectionId' on table 'SectionMembers' 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 or index. See previous errors.

Can you show us the SQL used to create those foreign keys? I'm assuming some of the statements will work, up to a certain point - and then one will fail. Hard to say without seeing exactly what you're doing in your SQL .... — marc_s, Aug 16 '20 at 07:38

score 1 · Accepted Answer · answered Aug 16 '20 at 08:32

Has multiple Accounts

An Account can be member of multiple Organizations => OrganizationMember

An Organization has multiple Sections

An OrganizationMember can be member of multiple Sections => SectionMember

How about this layout instead

Has N Accounts => Account (PK account_id)
Has N Organizations => Organization (PK organization_id)
An Organization has 1..N Sections => Section (PK section_id, FK organization_id NOT NULL)
An Account can be member of multiple Sections => AccountSection (PK (FK account_id, FK section_id))

In the end the account is always a member of a section, and it's a member of an organization only implicitly (whether it is or not can be determined through a simple JOIN).

How to prevent error: Introducing FOREIGN KEY constraint on table may cause cycles or multiple cascade paths

1 Answers1