引入FOREIGN KEY约束可能导致循环或多个级联路径。指定删除无操作 [英] Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths. Specify on delete no action

问题描述

用户，雇主，候选人和工作，雇主可以创建多个工作，每个工作只能有一个雇主，候选人可以申请许多工作，每个工作可以有多个应用成员。

User, Employer, Candidates and Job, an employer can create multiple jobs and each job can only have one employer, a candidate can apply for many jobs and each job can have multiple applied members.

所以关系就像这样：

我使用实体框架代码第一种方法，目前如果我删除雇主，它将删除所有相关职位和用户从数据库，如果我删除候选人，它将删除用户：

I am using entity framework code first approach, at the moment if I delete an employer, it is going to delete all related jobs and the user from database, and if I delete candidate, it is going to delete the user:

modelBuilder.Entity<Employer>()
     .HasRequired(e => e.User)
    .WithOptional(e => e.Employer).WillCascadeOnDelete();

//member is candidate
modelBuilder.Entity<Member>()
    .HasRequired(e => e.User)
    .WithOptional(e => e.Member).WillCascadeOnDelete();

modelBuilder.Entity<Employer>()
    .HasMany(a => a.Jobs)
    .WithRequired(b => b.Employer)
    .WillCascadeOnDelete();

一切正常，除非我指定候选人和工作之间的多对多关系，并使用更新数据库，它给我这个错误：

Everything works fine except when I specify many to many relationship between candidates and job and update the database using "update-database", it gives me this error:

在'MemberJobMap'表上引入FOREIGN KEY约束'FK_dbo.MemberJobMap_dbo.Jobs_JobId'可能会导致循环或多级联路径。指定ON DELETE NO ACTION或ON UPDATE NO ACTION或修改其他FOREIGN KEY约束。
无法创建约束。请参阅之前的错误。

以下是我如何指定多个关系：

Here is how I specified many to many relationship:

modelBuilder.Entity<Member>()
   .HasMany(m => m.Jobs)
   .WithMany(j => j.Members)
   .Map(c =>
   {
      c.MapLeftKey("Id");
      c.MapRightKey("JobId");
      c.ToTable("MemberJobMap");
   });

，当我添加迁移时：

CreateTable(
   "dbo.MemberJobMap",
   c => new
   {
      Id = c.String(nullable: false, maxLength: 128),
      JobId = c.Int(nullable: false),
   })
   .PrimaryKey(t => new { t.Id, t.JobId })
   .ForeignKey("dbo.Members", t => t.Id, cascadeDelete: true)
   .ForeignKey("dbo.Jobs", t => t.JobId, cascadeDelete: true)
   .Index(t => t.Id)
   .Index(t => t.JobId);   

我试图将cascadeDelete更改为false，但是当我删除已应用作业的候选人时，当我尝试删除应用候选人的工作。

I tried changing cascadeDelete to false but that gives me error when I delete a candidate that have applied jobs or when I try to delete a job with applied candidates.

如何解决这个错误？所以：

How to fix this error? So that:

1. 当一个作业被删除时，它将删除关联的
   candidatejobmap表行，而不会影响任何其他表


2. 删除候选项时，将删除关联的
   candidatejobmap表行和用户表行，而不会影响任何
   其他表


3. 同时保留所有其他指定的级联删除操作
   相同

推荐答案

我已经解决了这个问题

出现此问题是因为我有两个级联删除路径CandidateJobMap表：

The problem occurs because I have two cascading delete path to the CandidateJobMap table:

如果我删除雇主，它将删除相关的雇主工作，这将反过来删除CandidateJobMap表：

If I delete employer, its going to delete related employer jobs which will in turn delete CandidateJobMap table:

Employer-> Jobs-> CandidateJobMap

Employer->Jobs->CandidateJobMap

如果我删除候选，它将删除CandidateJobMap表：

If I delete candidate, its going to delete CandidateJobMap table:

Member-> CandidateJobMap

Member->CandidateJobMap

为了解决这个问题，我必须禁用一个删除路径，你不能指定WillCascadeDelete（false）当你创建多​​对多关系，所以，你必须改变迁移如下：

So to get around this problem, I have to disable one of the delete path, you cannot specify WillCascadeDelete(false) when you are creating many to many relations, so instead you have to change migration as follows:

CreateTable(
   "dbo.MemberJobMap",
   c => new
   {
      Id = c.String(nullable: false, maxLength: 128),
      JobId = c.Int(nullable: false),
   })
   .PrimaryKey(t => new { t.Id, t.JobId })
   .ForeignKey("dbo.Members", t => t.Id, cascadeDelete: false) <--------cascade delete to false
   .ForeignKey("dbo.Jobs", t => t.JobId, cascadeDelete: true)
   .Index(t => t.Id)
   .Index(t => t.JobId);   

现在因为你设置级联删除为false，当候选人被删除时，相关的CandidateJobMap行，这将导致另一个错误，当你尝试删除一个候选人，它也是一个相关的关键在CandidateJobMap，所以你必须手动删除CandidateJobMap相关行，然后删除候选人：

Now because you set cascade delete to false, when a candidate has been deleted, it won't delete related CandidateJobMap rows, this will cause another error when you try to delete a candidate where it is also a related key in CandidateJobMap, so you have to manually delete related rows in CandidateJobMap before removing the candidate:

//remove all applied jobs from user, without doing this, you will receive an error
foreach (var appliedjob in user.Member.Jobs.ToList())
{
    user.Member.Jobs.Remove(appliedjob);
}

//before you can delete the user
await UserManager.DeleteAsync(user);

不知道这是最好的方法，但它对我有用。

Not sure if this is the best way, but it worked for me.

这篇关于引入FOREIGN KEY约束可能导致循环或多个级联路径。指定删除无操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！