引进国外KEY约束可能会导致循环或多重级联路径。指定要在删除无动作 [英] Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths. Specify on delete no action

问题描述

用户，用人单位，考生和工作，雇主可以创建多个就业机会，每​​个作业只能有一个雇主，候选人可以申请许多工作，每个作业可以有多个应用的​​成员。

所以关系是这样的：

我使用实体框架code第一的方针，此刻如果我删除雇主，它会删除所有相关的工作，并从数据库中的用户，如果我删除候选人，它会删除用户：

  modelBuilder.Entity＆LT;雇主及其GT;（）
     .HasRequired（E =＆GT; e.User）
    .WithOptional（E =＆GT; e.Employer）.WillCascadeOnDelete（）;//成员候选人
modelBuilder.Entity＆LT;＆成员GT;（）
    .HasRequired（E =＆GT; e.User）
    .WithOptional（E =＆GT; e.Member）.WillCascadeOnDelete（）;modelBuilder.Entity＆LT;雇主及其GT;（）
    .HasMany（一个= GT; a.Jobs）
    .WithRequired（B = GT; b.Employer）
    .WillCascadeOnDelete（）;
 

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

引进国外KEY约束对表'FK_dbo.MemberJobMap_dbo.Jobs_JobId'MemberJobMap'可能会导致循环或多重级联路径。指定ON DELETE NO ACTION或ON UPDATE NO ACTION，或修改其他FOREIGN KEY约束。
无法创建约束。见previous错误。

下面是我指定的多对多的关系：

  modelBuilder.Entity＆LT;＆成员GT;（）
   .HasMany（M =＆GT; m.Jobs）
   .WithMany（J =＆GT; j.Members）
   .MAP（C =＆GT;
   {
      c.MapLeftKey（ID）;
      c.MapRightKey（的JobId）;
      c.ToTable（MemberJobMap）;
   }）;
 

和当我添加迁移：

  CREATETABLE（
   dbo.MemberJobMap
   C =＆GT;新
   {
      ID = c.String（可空：假的，最大长度：128）
      的JobId = c.Int（可空：假），
   }）
   .PrimaryKey（T =＆gt;新建{t.Id，t.JobId}）
   .ForeignKey（dbo.Members，T =＆GT; t.Id，cascadeDelete：真）
   .ForeignKey（dbo.Jobs，T =＆GT; t.JobId，cascadeDelete：真）
   的.index（T =＆GT; t.Id）
   的.index（T =＆GT; t.JobId）;
 

我试图改变cascadeDelete为false，但给我的错误，当我删除那些已经申请职位的候选人或当我尝试删除工作与应用的候选者。

如何解决这个问题？使得：

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


2. 当候选人被删除，它会删除相关的
   candidatejobmap表行以及用户表行没有任何影响
   其他表


3. 同时保持所有其他指定的级联删除动作
   同样

解决方案

我有固定的这个问题。

时，会出现问题，因为我有两个级联删除路径CandidateJobMap表：

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

雇主 - > Jobs-> CandidateJobMap

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

成员 - > CandidateJobMap

因此​​，要解决这个问题，我必须禁用删除路径之一，你不能指定WillCascadeDelete（假）当您创建多对多的关系，所以不是你要改的迁移如下：

  CREATETABLE（
   dbo.MemberJobMap
   C =＆GT;新
   {
      ID = c.String（可空：假的，最大长度：128）
      的JobId = c.Int（可空：假），
   }）
   .PrimaryKey（T =＆gt;新建{t.Id，t.JobId}）
   .ForeignKey（dbo.Members，T =＆GT; t.Id，cascadeDelete：假）＆LT; --------级联删除假
   .ForeignKey（dbo.Jobs，T =＆GT; t.JobId，cascadeDelete：真）
   的.index（T =＆GT; t.Id）
   的.index（T =＆GT; t.JobId）;
 

现在，因为你设置级联删除假，当候选人已被删除，不会删除相关CandidateJobMap行，这将导致另一个错误，当您试图删除的候选人那里也是CandidateJobMap相关的关键，所以你必须删除候选人之前，需要手动删除CandidateJobMap相关行：

  //删除用户所有的应用工作，不这样做，你将收到一个错误
的foreach（在user.Member.Jobs.ToList变种appliedjob（））
{
    user.Member.Jobs.Remove（appliedjob）;
}//然后才能删除用户
等待UserManager.DeleteAsync（用户）;
 

不知道这是最好的方式，但它为我工作。

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.

So the relationship is like this:

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:

Introducing FOREIGN KEY constraint 'FK_dbo.MemberJobMap_dbo.Jobs_JobId' on table 'MemberJobMap' 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. See previous errors.

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");
   });

and when I add migration:

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);   

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. When a job is removed, it is going to remove associated candidatejobmap table rows without effecting any other table
2. When a candidate is removed, it is going to remove associated candidatejobmap table rows and user table row without effecting any other table
3. While keeping all other specified cascade delete action the same

解决方案

I have fixed this problem

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

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

Employer->Jobs->CandidateJobMap

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

Member->CandidateJobMap

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);   

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.

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