CODEWITHSUNDEEP
c#.netdatabaseentity-frameworkrelational-database
Jordan Axe
Jordan Axe

Reputation: 3923

Foreign key constraint issue?

I've made some changes to my EF code first database and when I try to update it I now get the following error:

Introducing FOREIGN KEY constraint 'FK_dbo.SupportTicketMessages_dbo.SupportTickets_Ticket_Id' on table 'SupportTicketMessages' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

Here are my entities:

SupportTicket:

public class SupportTicket
    {
        [Key]
        [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }

        [Required]
        public string Title { get; set; }

        [Required]
        public string Text { get; set; }

        [Required]
        public TicketUrgency Urgency { get; set; }

        [Required]
        public TicketStatus Status { get; set; }

        [Required]
        public virtual UserProfile Owner { get; set; }

        [Required]
        public DateTime Date { get; set; }
}

SupportTicketMessage:

public class SupportTicketMessage
    {
        [Key]
        [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }

        [Required]
        public virtual UserProfile Author { get; set; }

        [Required]
        public string Text { get; set; }

        [Required]
        public DateTime Date { get; set; }

        [Required]
        public virtual SupportTicket Ticket { get; set; }

        [Required]
        public int MessageNumber { get; set; }
    }

What's the issue here? I don't see what's wrong.

Upvotes: 1

Views: 371

Answers (1)

Slauma
Slauma

Reputation: 177133

Problem is the required UserProfile in both entities.

You have three required one-to-many relationships between UserProfile, SupportTicket and SupportTicketMessage:

  1. one UserProfile - many SupportTickets
  2. one UserProfile - many SupportTicketMessages
  3. one SupportTicket - many SupportTicketMessages

(Left side is the principal (that has the primary key of the relationship) and right side is the dependent (that has the foreign key of the relationship).)

For required one-to-many relationships EF adds a cascading delete to the database by default, i.e. if the entity on the left side above is deleted all dependent entities on the right side should be deleted automatically.

If you would delete a UserProfile now you will have two cascading delete paths to the SupportTicketMessage table, namely:

  • UserProfile -> SupportTicket -> SupportTicketMessage (because of relationships 1 and 3)
  • UserProfile -> SupportTicketMessage (because of relationship 2)

This is not allowed in SQL Server and the reason for the exception.

In order to fix the problem you must "break" the cascading delete paths for at least one of the relationships. You can do that by either making the relationship optional (that is: remove one of the [Required] attributes on the navigation properties) or by disabling cascading delete explicitly. I would choose the latter option because changing a relationship from required to optional is kind of changing a business rule. I'd disable cascading delete for relationships 1 and 2 because, maybe, deleting a UserProfile should not delete all SupportTickets and SupportTicketMessages but instead the tickets and messages should be assigned to some "anonymous" default user, so the ticket history doesn't get lost when a user wants to leave the system.

Cascading delete must be disabled with Fluent API:

modelBuilder.Entity<SupportTicket>()
    .HasRequired(s => s.Owner)
    .WithMany()
    .WillCascadeOnDelete(false);

modelBuilder.Entity<SupportTicketMessage>()
    .HasRequired(s => s.Author)
    .WithMany()
    .WillCascadeOnDelete(false);

(If you have navigation collections in UserProfile refering to SupportTickets and SupportTicketMessages you must use the WithMany call with a lambda parameter, like WithMany(u => u.SupportTickets) and WithMany(u => u.SupportTicketMessages).)

Upvotes: 2

Related Questions