Possible issue in Sqlite unique constraint error handling

[sm15455]

Libraries Microsoft.EntityFrameworkCore.Sqlite 6.0.6 EntityFrameworkCore.Exceptions.Sqlite Version 6.0.3

I have a simple asp.net project correctly configured for exceptions

builder.Services.AddDbContext<NorthwindContext>(o =>
{
    o.EnableSensitiveDataLogging();
    o.UseSqlite("data source=northwind.db");
    o.UseExceptionProcessor();
});

I have the Customers table with a CustomerId primary key and the Orders table with a CustomerId foreign key. When I try to delete a customer with associated orders, the operation obviously fails. The SqliteErrorCode is 19 and SqliteExtendedErrorCode is 1811.

The problem is the SqliteExceptionProcessorInterceptor class doesn't handle the 1811 value in the switch, therefore in my code I receive a DbUpdateException and not a ReferenceConstraintException. Should the 1811 (const SQLITE_CONSTRAINT_TRIGGER) value be included in the switch?

[Giorgi]

@sm15455 Is SQLITE_CONSTRAINT_TRIGGER error code thrown only when you are trying to delete parent entity or is it triggered in other cases too? According to documentation

The SQLITE_CONSTRAINT_TRIGGER error code is an extended error code for SQLITE_CONSTRAINT indicating that a RAISE function within a trigger fired, causing the SQL statement to abort.

If this error code can be thrown in other situations too it doesn't make sense to treat it as ReferenceConstraintException

[sm15455]

Honestly I'm not an expert in Sqlite, but this kind of error look a lot like a reference constraint problem. I understand that if this is a more generic error that's involved even in other cases, it might be a problem for a generic library like this. This is why I say in the title that this is a possible issue and not a bug :). So far I installed the common package, inherited from ExceptionProcessorInterceptor and overrode the GetDatabaseError to fit my needs.

[Giorgi]

@sm15455 I tried to reproduce your case and I get 787 SQLITE_CONSTRAINT_FOREIGNKEY extended error code when I try to delete a parent entity that has a child entity:

using (var connection = new SqliteConnection("Data Source=:memory:"))
{
    var cmd = connection.CreateCommand();
    connection.Open();
    cmd.CommandText = @"CREATE TABLE artist(artistid INTEGER PRIMARY KEY, artistname  TEXT); ";
    cmd.ExecuteNonQuery();

    cmd.CommandText = @"CREATE TABLE track(trackid INTEGER,  trackname TEXT, trackartist INTEGER, FOREIGN KEY(trackartist) REFERENCES artist(artistid) );";
    cmd.ExecuteNonQuery();

    cmd.CommandText = "Insert into artist (artistname) values (\"Dean Martin\")";
    cmd.ExecuteNonQuery();

    cmd.CommandText = "Insert into track (trackname, trackartist) values (\"That's Amore\", 1)";
    cmd.ExecuteNonQuery();

    try
    {           
           cmd.CommandText = "DELETE FROM artist WHERE artistname = 'Dean Martin'";
           cmd.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
           Console.WriteLine(ex.SqliteExtendedErrorCode);
    }
}

SQLite version: 3.35.5 Microsoft.Data.Sqlite: 6.0.6

Can you double-check that you don't have any trigger that could be throwing an error?

[Giorgi]

Also, see: https://www.sqlite.org/foreignkeys.html It also suggest that the error code in all cases should be SQLITE_CONSTRAINT_FOREIGNKEY

[sm15455]

I have no triggers as the image shows. I'll try to adapt your code to my database and see if something different comes up.

[Giorgi]

@sm15455 Did you manage to reproduce the same error code?

[sm15455]

@Giorgi after further digging I think I got it. I generated the sqlite database using a migration tool from Sql Server I can't even remember. Foreign keys have been generated using "On Delete Restrict".

CREATE TABLE "Order Details" ( "OrderID" INTEGER NOT NULL, "ProductID" INTEGER NOT NULL, "UnitPrice" money NOT NULL DEFAULT (0), "Quantity" INTEGER NOT NULL DEFAULT (1), "Discount" REAL NOT NULL DEFAULT (0), CONSTRAINT "FK_Order_Details_Orders" FOREIGN KEY("OrderID") REFERENCES "Orders"("OrderID") ON DELETE RESTRICT, CONSTRAINT "FK_Order_Details_Products" FOREIGN KEY("ProductID") REFERENCES "Products"("ProductID") ON DELETE RESTRICT, CONSTRAINT "PK_Order_Details" PRIMARY KEY("OrderID","ProductID") );

The "On Delete Restrict" clause probably adds an internal trigger that is not visible in the editor and causes the 1181 instead of the 787. When I regenerated the table from scratch removing the "On Delete Restrict" clause, I got 787 error code.

[Giorgi]

Nice to hear that you find the root cause of the issue. I'll close this issue as it is resolved now.

[sm15455]

To me, it still feels strange that a foreign key problem is not handled by the library, but I totally understand that it's difficult to catch edge cases like these. Thanks for your support.

[Giorgi]

What do you mean by foreign key problem is not handled by the library ? The library handles 787 error code from Sqlite.

[sm15455]

I apologize, I didn't explain myself correctly. The library correctly catches Foreign Key errors. I meant that in such case ("On Delete Restrict"), even 1811 is a foreign key error, but it's not handled. I don't think you can arbitrarily assume that 1811 is always a foreign key problem so there's little you can do. In my application I'll keep on treating 1811 as foreign key error until I'll be able to remove the "On Delete Restrict" everywhere.