Enable foreign key constraint checks for Sqlite
Currently OrmLite's Sqlite adapter supports creation of foreign key constraints when using the ForeignKey attribute on your data classes. However the foreign keys aren't actually enforced at the database by default (for 'compatibility reasons').
I don't know when Sqlite added the feature, but it will check constraints if you send a pragma at the start of each connection:
http://www.sqlite.org/pragma.html#pragma_foreign_keys
I've tested a simple workaround by sending the pragma as a SQL statement in my service methods), and the constraints are enforced as expected. Cascade deletes work as expected too.
However this isn't optimal if you want your service code to be db-agnostic (it's also a bit inefficient if sent as a separate db request, rather than pre-pended to the first request). It would be great to have this enabled at the dialect level so that developers can use db-agnostic code and patterns related to constraint checks and foreign key delete and update operations, regardless whether they are using Sqlite or the other databases that support foreign keys.
Brent
shared this idea
The EnableForeignKeysCheck/Async and DisableForeignKeysCheck/Async APIs are the RDBMS agnostic APIs for enabling/disabling FK constraints which in SQLite sets the foreign_keys PRAGMA.
There’s no state or concept of “prepending to first db request”, the right time to call it is after opening a DB Connection which you can wrap in your own Custom SqliteOrmLiteConnectionFactory as Franck shows in comments or behind a custom IDbConnection extension method that does it or if you want it called everytime you can register it on the IOrmLiteDialectProvider’s OnOpenConnection delegate which is also available on the OrmLiteConnectionFactory’s ConnectionFilter.
-
Franck Quintana
commented
Another method is to use an override of OrmLiteConnectionFactory like this:
private class SqliteOrmLiteConnectionFactory : OrmLiteConnectionFactory
{
public SqliteOrmLiteConnectionFactory() : base(":memory:", SqliteDialect.Provider)
{
}public override System.Data.IDbConnection OpenDbConnection()
{
var db = base.OpenDbConnection();
db.ExecuteSql("PRAGMA foreign_keys = ON;");
return db;
}
}