0

I'm developing a DataBase on MS SQL Server with EF 6 - Code First I have schema like so: Ads|Messages|Attachments

where

Ads has many Messages Ads has many Attachments

Messages has many Attachments

The Attachments table has 2 FKs one to Ads and one to Messages. The Messages table has 1FK to Ads.

I want to create Cascading Rule Where when i delete Ad it deletes all messages for this Ad with their Attachments(Message Attachments), and the Ad's Attachments, but i'm failing so far.

I get

Introducing FOREIGN KEY constraint 'FK_dbo.Attachments_dbo.Messages_MessageId' on table 'Attachments' 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.

The ModelBuilder Code is the Following

        modelBuilder.Entity<Attachments>()
            .HasOptional(a => a.Advertisement)
            .WithMany(a => a.Attachments)
            .WillCascadeOnDelete(true);

        modelBuilder.Entity<Attachments>()
            .HasOptional(a => a.Message)
            .WithMany(a => a.Attachments)
            .WillCascadeOnDelete(true);

        modelBuilder.Entity<Messages>()
            .HasRequired(m => m.Advertisement)
            .WithMany(u => u.Messages)
            .WillCascadeOnDelete(true);
Community
  • 1
  • 1
Stefan PEev
  • 481
  • 5
  • 17
  • 1
    You've created a "triangular relationship". If Ads is related to Messages, and Messages is related to Attachments then, a relationship between Ads and Attachments is already defined. You should not then be defining a further relationship between Ads and Attachments. – Thom A Apr 12 '18 at 11:05
  • But i want my Ads to have Attachments as well as the Messages – Stefan PEev Apr 12 '18 at 11:11
  • So, can an Ad have attachments, but have no messages? – Thom A Apr 12 '18 at 11:12
  • Yes, thats the start point. User is Creating Ad with some attachments. Then the other users write messages to the Ad with their own attachments respectively. – Stefan PEev Apr 12 '18 at 11:14
  • I'm familiar with : The series of cascading referential actions triggered by a single DELETE or UPDATE must form a tree that contains no circular references. No table can appear more than one time in the list of all cascading referential actions that result from the DELETE or UPDATE. Also, the tree of cascading referential actions must not have more than one path to any specified table. Any branch of the tree is ended when it encounters a table for which NO ACTION has been specified or is the default. but i thought that there might be some workaround – Stefan PEev Apr 12 '18 at 11:16
  • Then the design you have at the moment won't work, for the reason I original specified. One solution would be to have different tables for attachments linked directly to Ads, and those linked to messages. – Thom A Apr 12 '18 at 11:16
  • Wouldn't it be bad to make the same table twice only because of this ? – Stefan PEev Apr 12 '18 at 11:18
  • 1
    It's a far less of an evil that having a "triangular relationship". – Thom A Apr 12 '18 at 11:22

0 Answers0