How to ignore Foreign Key Constraints in Entity Framework Core SQLite database?

Question

Foreign Key constraint failed use SQLite with Entity Framework Core

I have relations in table

[Table("organizations")]
public class Organizations
{
    [Column("id")]
    public int Id { get; set; }

    [Column("parent_id")]
    [ForeignKey("id")]
    public int? ParentId { get; set; }

    [Column("person_id")]
    public int? PersonId { get; set; }
 }


public DbSet<Organizations> Organizations { get; set; }

using (var db = new SQLiteDbContext($"Filename={dbPath};"))
{
    db.Database.ExecuteSqlCommand("PRAGMA foreign_keys=OFF;");
    db.Database.ExecuteSqlCommand("PRAGMA ignore_check_constraints=true;");
    db.Organizations.AddRange(organizationsResult.Rows);
}

I get an error from the Sqlite database:

{"SQLite Error 19: 'FOREIGN KEY constraint failed'"}

bricelam · Accepted Answer · 2020-06-20 23:41:21Z

29

The PRAGMA looses effect if the connection is closed. You need to increase the lifetime of the connection by calling db.Database.OpenConnection() and CloseConnection() before and after.

You can also call optionsBiulder.UseSqlite(connectionString, x => x.SuppressForeignKeyEnforcement()) to prefent EF from automatically turning foreign key enforcement on per connection.

Entity Framework Core 3 Update:

For EF Core 3.0 SuppressForeignKeyEnforcement has been removed. see learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.0/…

Use "Foreign Keys = False" in the connection string instead. Eg.

connectionString = "Data Source=Data.db;Password=yourpassword;Foreign Keys=False"

edited Jun 20, 2020 at 23:41

user12425844

answered Oct 27, 2016 at 15:58

bricelam

30.7k9 gold badges97 silver badges119 bronze badges

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

3 Comments

Brett Manners Over a year ago

For EF Core 3.0 SuppressForeignKeyEnforcement has been removed. see learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.0/… Use "Foreign Keys = False" in the connection string instead. Eg. connectionString = "Data Source=Data.db;Password=yourpassword;Foreign Keys=False"

Sudhu Over a year ago

Foreign Keys = False, this doesn't seem to be working on .NET 6 for SQL server. Any other possible ways to ignore Foreign Key? @BrettManners

bricelam Over a year ago

@Sudhu You're looking for issue #15854

Collectives™ on Stack Overflow

How to ignore Foreign Key Constraints in Entity Framework Core SQLite database?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related