SQLite PRAGMA foreign_keys question

Question

I've modified System.Data.SQLite to use a recent version of SQLite engine that automatically enforces foreign keys without using custom triggers.

I'm also using SubSonic 2.x but this would apply to any ORM frameworks using SQLite that are 'open late close early'.

How would you ensure that the statement 'PRAGMA foreign_keys=true' is called on every SQLiteConnection.Open()? It has to be called or foreign keys don't work.

Answer 1

To fix this, I added a 'Foreign Keys' property to ConnectionString in SQLiteConnection class.

Foreign Keys=ON Foreign Keys=OFF

Answer 2

You don't need to modify System.Data.SQLite if you want to use the latest version of SQLite, just use the ManagedOnly version of System.Data.SQLite and then only replace the sqlite3.dll with the latest version. For enabling foreign key support I simply execute a sql statement that enables foreign key support. e.g.

        string databasePath = "Your database path here";
        string connectionString = "Data Source=" + databasePath;
        connection = new SQLiteConnection(connectionString);
        connection.Open();

        const string sqlString = "PRAGMA foreign_keys = ON;";
        SQLiteCommand command = new SQLiteCommand(sqlString, connection);
        command.ExecuteNonQuery();