How to enable foreign key cascade delete by default in SQLite?

Question

SQLite v3.7.5

Is there a way to enable SQLite Foreign Keys with cascade delete enabled by default? Given the following example:

CREATE TABLE [Parent] (
[ParentId] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
[Name] VARCHAR(50)  UNIQUE NOT NULL
);

CREATE TABLE [Child] (
[ChildId] INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
[ParentId] INTEGER  NOT NULL,
[Name] VARCHAR(50)  NOT NULL,
FOREIGN KEY(ChildId) REFERENCES Child(ParentId) ON DELETE CASCADE
);

The only way I've been able to enable the cascade delete is to execute the PRAGMA foreign_keys = true command before a transaction:

using( var conn = new SQLiteConnection( _conn ) )
{
    conn.Open();
    var pragma = new SQLiteCommand( "PRAGMA foreign_keys = true;", conn );
    pragma.ExecuteNonQuery();

    var cmd = new SQLiteCommand( "Delete from Parent where ParentId = 1", conn );
    cmd.ExecuteNonQuery();
}

Is there a setting on the database level that can be configured rather than having to call the pragma command before each transaction?

I've seen the triggers to enable cascade deletes, but am looking for something that would simply enable the PRAGMA foreign_keys = true at the database level.

Answer 1

System.Data.SQLite 1.0.66 doesn't have it, but in the repository version of it, they have updated to sqlite 3.7.4 and have made a new connection string attribute "Foreign Keys". Who knows when this will be released officially? So you could set this in your connection string. The project lives here now: http://system.data.sqlite.org/index.html/doc/trunk/www/index.wiki