ASP.NET Core - the DELETE statement conflicted with the REFERENCE constraint . The conflict occurred in database

Question

I'm trying to solve this problem for hours. I believe it's some kind of relationship problem.

I get this error:

The DELETE statement conflicted with the REFERENCE constraint "FK_NoteTag _Notes_NoteId". The conflict occurred in database "db", table "dbo.NoteTag", column 'NoteId'.

These are my model classes:

public class Group
{
    public Group()
    { }
    
    public Group(int groupId, string groupName)
    {
        GroupId = groupId;
        GroupName = groupName;
    }
    
    [Key] 
    public int? GroupId { get; set; }
    [Required] 
    public string GroupName { get; set; }

    public virtual User User { get; set; }
    public virtual List<Note> Notes { get; set; }
    public virtual IList<GroupTag> GroupTag { get; set; }
}

public class GroupTag
{
    public GroupTag()
    { }

    public GroupTag(int groupId, Group group, int tagId, Tag tag)
    {
        GroupId = groupId;
        Group = group;
        TagId = tagId;
        Tag = tag;
    }
            
    public virtual int? GroupId { get; set; }
    public virtual Group Group { get; set; }
    public virtual int? TagId { get; set; }
    public virtual Tag Tag { get; set; }
}

public class Note
{
    public Note()
    { }

    public Note(int noteId, string noteName, string noteText, DateTime noteDuration, DateTime noteCreation)
    {
        NoteId = noteId;
        NoteName = noteName;
        NoteText = noteText;
        NoteDuration = noteDuration;
        NoteCreation = noteCreation;
    }

    [Key] 
    public int? NoteId { get; set; }
    [Required] 
    public string NoteName { get; set; }

    public string NoteText { get; set; }
    public DateTime NoteDuration { get; set; }
    public DateTime NoteCreation { get; set; }

    public virtual Group Group { get; set; }
    public virtual IList<NoteTag> NoteTag { get; set; }
}

public class NoteTag
{
    public NoteTag()
    { }

    public NoteTag(int noteId, Note note, int tagId, Tag tag)
    {
        NoteId = noteId;
        Note = note;
        TagId = tagId;
        Tag = tag;
    }

    public virtual int? NoteId { get; set; }
    public virtual Note Note { get; set; }
    public virtual int? TagId { get; set; }
    public virtual Tag Tag { get; set; }
}

public class Tag
{
    public Tag()
    { }

    public Tag(int tagId, string tagName)
    {
        TagId = tagId;
        TagName = tagName;
    }

    [Key] 
    public int? TagId { get; set; }
    [Required] 
    public string TagName { get; set; }

    // owner
    public virtual User User { get; set; }

    public virtual IList<NoteTag> NoteTag { get; set; }
    public virtual IList<GroupTag> GroupTag { get; set; }
}

public class User
{
    public User()
    { }

    public User(int userId, string nickname, string password, bool admin)
    {
        UserId = userId;
        Nickname = nickname;
        Password = password;
        Admin = admin;
    }

    [Key] 
    public int? UserId { get; set; }
    [Required] 
    public string Nickname { get; set; }
    [Required] 
    public string Password { get; set; }
    [Required] 
    public bool Admin { get; set; }

    public virtual List<Tag> Tags { get; set; }
    public virtual IList<Group>  Groups { get; set; }
}

And here is the DB context:

 //M:M keys (1/3) NoteTag
            modelBuilder.Entity<NoteTag>().HasKey(nt => new {nt.NoteId, nt.TagId});

            //M:M keys (2/3)
            modelBuilder.Entity<NoteTag>()
                .HasOne<Note>(nt => nt.Note)
                .WithMany(t => t.NoteTag)
                .OnDelete(DeleteBehavior.ClientSetNull);

            //M:M keys (3/3)
            modelBuilder.Entity<NoteTag>()
                .HasOne<Tag>(nt => nt.Tag)
                .WithMany(n => n.NoteTag)
                .OnDelete(DeleteBehavior.ClientSetNull);
            //
            //M:M keys (1/3) GroupTag
            modelBuilder.Entity<GroupTag>().HasKey(gt => new {gt.GroupId, gt.TagId});

            //M:M (2/3)
            modelBuilder.Entity<GroupTag>()
                .HasOne<Group>(gt => gt.Group)
                .WithMany(t => t.GroupTag)
                .OnDelete(DeleteBehavior.ClientSetNull);

            //M:M (3/3)
            modelBuilder.Entity<GroupTag>()
                .HasOne<Tag>(gt => gt.Tag)
                .WithMany(g => g.GroupTag)
                .OnDelete(DeleteBehavior.ClientSetNull);
            
            modelBuilder.Entity<Group>()
                .HasMany(g => g.Notes)
                .WithOne(u => u.Group)
                .OnDelete(DeleteBehavior.Cascade);
            
            modelBuilder.Entity<User>()
                .HasMany(u => u.Groups)
                .WithOne(g => g.User)
                .OnDelete(DeleteBehavior.Cascade);
            
            modelBuilder.Entity<User>()
                .HasMany(u => u.Tags)
                .WithOne(g => g.User)
                .OnDelete(DeleteBehavior.Cascade);
            
            
            modelBuilder.Entity<Note>()
                .HasData(
                    notes
                );

            modelBuilder.Entity<Tag>()
                .HasData(
                    tags
                );

            modelBuilder.Entity<User>()
                .HasData(
                    users
                );

            modelBuilder.Entity<Group>()
                .HasData(
                    groups
                );

            modelBuilder.Entity<NoteTag>()
                .HasData(
                    noteTag
                );

            modelBuilder.Entity<GroupTag>()
                .HasData(
                    groupTag
                );

And here is the seed of DB:

object[] users =
            {
                new
                {
                    UserId = 1, Nickname = "AwesomeUser", Password = "nicepass", Admin = true
                },
                new
                {
                    UserId = 2, Nickname = "LameUser", Password = "nopass", Admin = false
                },
                new
                {
                    UserId = 3, Nickname = "scooby", Password = "pass", Admin = false
                },
                new
                {
                    UserId = 4, Nickname = "doo", Password = "hezogeg", Admin = false
                }
            };

            object[] groups =
            {
                new {GroupId = 1, GroupName = "leden", UserId = 1},
                new {GroupId = 2, GroupName = "unor", UserId = 2},
                new {GroupId = 3, GroupName = "brezen", UserId = 3},
                new {GroupId = 4, GroupName = "duben", UserId = 4}
            };

            object[] notes =
            {
                new
                {
                    NoteId = 1,
                    NoteName = "Uvařit oběd",
                    NoteText = "1. Brambory 2. maso",
                    NoteDuration = DateTime.Now,
                    NoteCreation = new DateTime(2008, 3, 1, 7, 0, 0),
                    GroupId = 1
                },
                new
                {
                    NoteId = 2,
                    NoteName = "Naučit se programova",
                    NoteText = "- sjet C# tutorial",
                    NoteDuration = DateTime.Now,
                    NoteCreation = new DateTime(2020, 3, 1, 7, 0, 0),
                    GroupId = 2
                },
                new
                {
                    NoteId = 3,
                    NoteName = "B7",
                    NoteText = "Vyjít lysou",
                    NoteDuration = DateTime.Now,
                    NoteCreation = new DateTime(2018, 3, 1, 7, 0, 0),
                    GroupId = 3
                },
                new
                {
                    NoteId = 4,
                    NoteName = "B12",
                    NoteText = "Udělat salto",
                    NoteDuration = DateTime.Now,
                    NoteCreation = new DateTime(2019, 3, 1, 7, 0, 0),
                    GroupId = 4
                }
            };

            object[] tags =
            {
                new {TagId = 1, TagName = "Vaření", UserId = 1},
                new {TagId = 2, TagName = "Sport", UserId = 2},
                new {TagId = 3, TagName = "Sebe vyvoj", UserId = 3},
                new {TagId = 4, TagName = "Gymnastika", UserId = 4}
            };

            object[] noteTag =
            {
                new {NoteId = 1, TagId = 1},
                new {NoteId = 2, TagId = 2},
                new {NoteId = 3, TagId = 3},
                new {NoteId = 4, TagId = 4}
            };

            object[] groupTag =
            {
                new {GroupId = 1, TagId = 1},
                new {GroupId = 2, TagId = 2},
                new {GroupId = 3, TagId = 3},
                new {GroupId = 4, TagId = 4}
            };

Thank you for your help

Once check the primary key and foreign key relation ships in the database table. — N.Siva, Oct 28 '20 at 11:27
" I believe it's some kind of relationship problem"...yes. In a relational database, you can't delete a row from a table if there is a foreign key linking to a column in that table, and the value in that column is being used in the linked table. Because if you did, the other table would lose its integrity - there'd be no value to join back to, to make the record make sense. That's the whole point of such constraints, to stop you deleting data which is required by other tables. I suggest you take some time to learn more about this topic if you don't understand these key ideas. — ADyson, Oct 28 '20 at 11:29
@ADyson I suggest that adding Cascade Delete Behavior should handle this problem. So It would delete data from foreign table first and then delete from the first table it self. In that case the problem is in wrong implementation of behavior, am i right? Or a source of problem is somewhere else? — Patrik Kolenovský, Oct 28 '20 at 11:37
Adding cascade delete behaviour ought to help with this yes. You haven't added it to the table mentioned in the error. — ADyson, Oct 28 '20 at 11:52

score 0 · Answer 1 · answered Oct 28 '20 at 12:24

0

Finaly i've found the problem... I foreign key specification was required. I thought that fluent api solve naturely. Thank you guys

answered Oct 28 '20 at 12:24

Patrik Kolenovský

21
1

ASP.NET Core - the DELETE statement conflicted with the REFERENCE constraint . The conflict occurred in database

1 Answers1