如何防止错误:在表中引入外键约束可能导致循环或多个级联路径
如何防止错误:在表中引入外键约束可能导致循环或多个级联路径
提问于 2020-08-16 07:32:11
我试图为以下情况创建正确的数据库结构：
我的数据库：
- 有多个帐户
- 一个帐户可以是多个组织的成员=> =>组织有多个部门
- OrganizationMember可以是多个部门的=> SectionMember
的成员
我的级联删除的目的是：
当我删除一个节时，我要对所有连接的SectionMembers
- When进行级联删除SectionMembers
- When删除一个OrganizationMember，对所有已连接的OrganizationMembers删除一个组织，对所有连接的OrganizationMembers和各个节进行级联删除(包括它的SectionMembers)
- When删除一个帐户，对所有连接的OrganizationMembers(包括它的SectionMembers)
)进行级联删除)
我试图创建这样的表：
}
- Organization {ID，account_fields
- {ID，organization_fields }
- 节{ ID，OrganizationID，section_fields
- { ID，AccountID，OrganizationID，organizationMember_fields }
- SectionMember { ID，SectionID，OrganizationMemberID，sectionMember_fields }OrganizationMemberID
现在，当我创建表时，我得到了错误：
在表'SectionMembers‘上引入外键约束'FK_SectionMembers_Sections_SectionId’可能会导致循环或多个级联路径。
我找到了这个答案：https://stackoverflow.com/a/3548225。这似乎是我的情况，但建议的解决方案不能解决我的级联删除的目的。
问：是否有一种改变表结构的方法，以解决我对级联删除的目的？
由EF Core生成的SQL：
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.回答 1
Stack Overflow用户
回答已采纳
发布于 2020-08-16 08:32:14
OrganizationMember
- An
- 有多个帐户
- 一个帐户可以是多个组织的成员=>
- 组织有多个部门
- OrganizationMember可以是多个部门的成员
这样的布局怎么样？
有N个账户，Account (PK account_id)
- Has，N个组织，=>，
Organization (PK organization_id)
- An，组织有1个。N节，=>，
Section (PK section_id, FK organization_id NOT NULL)
- An，帐户可以是多个节的=>
AccountSection (PK (FK account_id, FK section_id))
的成员。
最后，帐户始终是某个部分的成员，并且它只是一个组织的成员(它是否是，可以通过一个简单的联接来确定)。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/63434300
复制相关文章
相似问题
腾讯云开发者
