On item remove, SQLite Error 19: 'FOREIGN KEY constraint failed

Question

I am using EF Core 9.0.2 (database-first) with the (unmodified) SQLite example chinook.db, for testing.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Album>(entity =>
    {
        entity.HasIndex(e => e.ArtistId, "IFK_AlbumArtistId");

        entity.Property(e => e.Quantity).HasDefaultValue(0);
        entity.Property(e => e.Title)
            .IsRequired()
            .HasColumnType("NVARCHAR(160)");

        entity.HasOne(d => d.Artist).WithMany(p => p.Albums)
            .HasForeignKey(d => d.ArtistId)
            .OnDelete(DeleteBehavior.ClientSetNull);
    });

When I delete an Album item, I get this exception:

SQLite Error 19: 'FOREIGN KEY constraint failed

I checked the database (original) and it's correct.

I have found several reports of this exception, but I can't resolve it.

This is the schema:

you said you are using the chinook.db sample database ... doesn't the album have a relationship with tracks that are expected to be deleted before deleting the parent album? can you enable cascade delete to overcome the need of explicitely delete the tree? — Diego D
– Diego D, Commented Mar 11 at 10:18
@DiegoD The Album has "many to one" relationships with the Artist table. If I remove the Album element, I don't change the Artist table, right? However I also tried the ".OnDelete(DeleteBehavior.ClientCascade)" option and doesn't work. — Brummell
– Brummell, Commented Mar 11 at 10:41
ok but you are ignoring the relationship between album and tracks.. you can't delete an album if there are existing records tracks belonging to it. You tried the cascade delete but if it didn't work I don't know how — Diego D
– Diego D, Commented Mar 11 at 10:49
@DiegoD You right. I honestly didn't notice, but at least I have something to study — Brummell
– Brummell, Commented Mar 11 at 10:52
You said you are using the sqlite db chinook.db as is. It does have a tracks table with a relationship with albums and there's no data model you can define to change that. — Diego D
– Diego D, Commented Mar 11 at 10:57

Brummell · Accepted Answer · 2025-03-11 16:25:25Z

0

You just need to change the connection string: "Foreign Keys=False"

    private static void LoadDbContext()

    {
        var connectionstring = "data source=D:\\Repos\\ERP_WPF\\ERP_WPF\\chinook.db;Foreign Keys=False";
        var optionsBuilder = new DbContextOptionsBuilder\<ChinookdbContext\>();
        optionsBuilder.UseSqlite(connectionstring);
        context = new ChinookdbContext(optionsBuilder.Options);
    }

https://stackoverflow.com/a/40289265/2064937

edited Mar 11 at 16:25

answered Mar 11 at 16:20

Brummell

2422 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

On item remove, SQLite Error 19: 'FOREIGN KEY constraint failed

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related