cancel
Showing results for 
Search instead for 
Did you mean: 

How to configure cascade delete where there are cycles and multiple cascade paths

Frequent Visitor

How to configure cascade delete where there are cycles and multiple cascade paths

I am having a bit of trouble getting my head around this relationship, and how to possibly setup cascade delete settings for it.

  • There is a table of employees, where each employee has any number of handles, attachments and jobs
  • There is a table of handles, where each handle belongs to an employee and may be used in a tool
  • There is a table of attachments, where each attachment belongs to an employee and may be used in a tool
  • 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
  • There is a table of jobs, where each job belongs to an employee, and may or may not have a tool used on it

Note: it's possible for handles and attachments to exist without being used to make a tool

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.

This diagram shows how the database is wired together (feel free to suggest a better design)

DB Diagram

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.

public class Employee
{
    public int EmployeeId { get; set; }
    public string Name { get; set; }

    public List<Handle> Handles { get; set; }
    public List<Attachment> Attachments { get; set; }
    public List<Job> Jobs { get; set; }
}
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<Tool> Tools { get; set; }
}
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<Tool> Tools { get; set; }
}
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<Job> Jobs { get; set; }
}
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; }
}

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

public class ToolsDbContext : DbContext
{
    public ToolsDbContext(DbContextOptions<ToolsDbContext> options) : base(options)
    {

    }

    public DbSet<Employee> employees { get; set; }
    public DbSet<Handle> handles { get; set; }
    public DbSet<Attachment> attachments { get; set; }
    public DbSet<Tool> tools { get; set; }
    public DbSet<Job> jobs { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Tool>()
            .HasMany(j => j.Jobs)
            .WithOne(t => t.Tool)
            .OnDelete(DeleteBehavior.SetNull);
    }
}

Creating the migration works, but then updating the database fails with the following error:

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.

I'm not too sure how to understand this error.

Thinking it through:

  • 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
  • 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
  • If a tool is deleted it will set ToolId in related Jobs to null
  • If a job is deleted, there will be no cascade effect

Therefore I think the problem must be with deleting an employee, but I can't see why (yet?)...

  • 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).

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...

Note: without the employees model in the database, everything seems to work

1 REPLY 1
Highlighted
Community Hero

Re: How to configure cascade delete where there are cycles and multiple cascade paths

Hi,

 

Any relation of the above to LoadNinja tool?

 

Regards,
Alex
[Community Expert Group]
____
[Community Expert Group] members are not employed by SmartBear Software but
are just volunteers who have some experience with the tools by SmartBear Software
and a desire to help others. Postings made by [Community Expert Group] members
may differ from the official policies of SmartBear Software and should be treated
as the own private opinion of their authors and under no circumstances as an
official answer from SmartBear Software.
[Community Expert Group] signature is used with permission by SmartBear Software.
http://smartbear.com/forums/f83/t86934/community-experts/
================================
New Here?
Join us and watch the welcome video:
Labels