title: How to configure cascade delete where there are cycles and multiple cascade paths
body: <p>I'm having a bit of trouble getting my head around this relationship, and how to possibly setup cascade delete settings for it.</p>
  
  <ul>
  <li>There is a table of employees, where each employee has any number of handles, attachments and jobs</li>
  <li>There is a table of handles, where each handle belongs to an employee and may be used in a tool</li>
  <li>There is a table of attachments, where each attachment belongs to an employee and may be used in a tool</li>
  <li>There is a table of tools, where each tool is made up of one attachment, one handle and is used on any number of jobs</li>
  <li>There is a table of jobs, where each job belongs to an employee, and may or may not have a tool used on it</li>
  </ul>
  
  <p><em>Note: it's possible for handles and attachments to exist without being used to make a tool</em></p>
  
  <p>In short: an employee can mix-and-match handles and attachments to make tools, and then use a tool on a job they are assigned.</p>
  
  <p>This diagram shows how the database is wired together (<em>feel free to suggest a better design</em>)</p>
  
  <p><a href="https://i.stack.imgur.com/XL04C.png" rel="nofollow noreferrer">DB Diagram</a></p>
  
  <p>This is how the models are setup, the Job model has a nullable reference to the tools FK (ToolId) so a job can exist without a tool.</p>
  
  <pre class="lang-cs prettyprint-override"><code>public class Employee
  {
      public int EmployeeId { get; set; }
      public string Name { get; set; }
  
      public List&lt;Handle&gt; Handles { get; set; }
      public List&lt;Attachment&gt; Attachments { get; set; }
      public List&lt;Job&gt; Jobs { get; set; }
  }
  </code></pre>
  
  <pre class="lang-cs prettyprint-override"><code>public class Handle
  {
      public int HandleId { get; set; }
      public string Material { get; set; }
      public double ExpectedLife { get; set; }
      public double LifetimeMaintenance { get; set; }
  
      public int EmployeeId { get; set; }
      public Employee Employee { get; set; }
      public List&lt;Tool&gt; Tools { get; set; }
  }
  </code></pre>
  
  <pre class="lang-cs prettyprint-override"><code>public class Attachment
  {
      public int AttachmentId { get; set; }
      public string Material { get; set; }
      public string Type { get; set; }
      public double ExpectedLife { get; set; }
      public double LifetimeMaintenance { get; set; }
  
      public int EmployeeId { get; set; }
      public Employee Employee { get; set; }
      public List&lt;Tool&gt; Tools { get; set; }
  }
  </code></pre>
  
  <pre class="lang-cs prettyprint-override"><code>public class Tool
  {
      public int ToolId { get; set; }
      public string OperationSpeed { get; set; }
  
  
      public int HandleId { get; set; }
      public Handle Handle { get; set; }
  
      public int AttachmentId { get; set; }
      public Attachment Attachment { get; set; }
  
      public List&lt;Job&gt; Jobs { get; set; }
  }
  </code></pre>
  
  <pre class="lang-cs prettyprint-override"><code>public class Job
  {
      public int JobId { get; set; }
      public string Name { get; set; }
      public double EffortRequired { get; set; }
  
      public int EmployeeID { get; set; }
      public Employee Employee { get; set; }
      public int? ToolId { get; set; }
      public Tool Tool { get; set; }
  }
  </code></pre>
  
  <p>This is how the DB context has been created. There is a cascade delete setting to set the tool FK in Jobs (ToolId) to null when a tool is deleted (so the job wont get deleted when its tool is deleted).</p>
  
  <pre class="lang-cs prettyprint-override"><code>public class ToolsDbContext : DbContext
  {
      public ToolsDbContext(DbContextOptions&lt;ToolsDbContext&gt; options) : base(options)
      {
  
      }
  
      public DbSet&lt;Employee&gt; employees { get; set; }
      public DbSet&lt;Handle&gt; handles { get; set; }
      public DbSet&lt;Attachment&gt; attachments { get; set; }
      public DbSet&lt;Tool&gt; tools { get; set; }
      public DbSet&lt;Job&gt; jobs { get; set; }
  
      protected override void OnModelCreating(ModelBuilder modelBuilder)
      {
          modelBuilder.Entity&lt;Tool&gt;()
              .HasMany(j =&gt; j.Jobs)
              .WithOne(t =&gt; t.Tool)
              .OnDelete(DeleteBehavior.SetNull);
      }
  }
  </code></pre>
  
  <p>Creating the migration works, but then updating the database fails with the following error:</p>
  
  <pre><code>Introducing FOREIGN KEY constraint 'FK_tools_handles_HandleId' on table 'tools' 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.
  </code></pre>
  
  <p>I'm not too sure how to understand this error.</p>
  
  <p>Thinking it through:</p>
  
  <ul>
  <li>If a handle is deleted, it will delete all tools it's used in, which in turn will set ToolId in related Jobs to null</li>
  <li>If an attachment is deleted, it will delete all tools it's used in, which in turn will set ToolId in related Jobs to null</li>
  <li>If a tool is deleted it will set ToolId in related Jobs to null</li>
  <li>If a job is deleted, there will be no cascade effect</li>
  </ul>
  
  <p>Therefore I think the problem must be with deleting an employee, but I can't see why (yet?)...</p>
  
  <ul>
  <li>If an employee is deleted everything should be deleted; it should delete all related jobs, handles and attachments. Then those deleted handles or attachments should in turn delete the tools associated with them (it shouldn't matter what came first). </li>
  </ul>
  
  <p>So there is cascade paths deleting an employee, but I would expect this would all work based on the model setup as-is... So do I need to configure more cascade delete requirements in the dbcontext? If so, I'm not sure how it should be configured...</p>
  
  <p><em>Note: without the employees model in the database, everything seems to work</em></p>