In c# compact-framework, how do you do a SQLite transaction while keeping foreign key constraints in an open connection?

Question

I am creating an application in Windows Forms .Net Compact Framework 3.5 with a backing SQLite database. I am needing to keep an open connection to the database for performance reasons, and I am needing to use transactions to execute multiple updates for the possibility of rollback.

There are foreign key constraints in some of the SQLite tables, and by default foreign key enforcement is off, so I had to manually turn them on when opening the connection from the application to the database.

The problem now is that transactions do not allow foreign key constraints: (https://sqlite.org/pragma.html#pragma_foreign_keys)

So when I run the the update statements, they fail and the transaction rolls back.

I tried manually turning the foreign key constraint off before the transaction, and then the updates succeeded. But when I tried turning the foreign key constraint back on after the transaction, the updates failed.

I need the foreign key constraints to be on, but they fail during transactions, and I cant simply turn them off, run a transaction, and then turn them back on. So what should I do?

using (var cmd1 = new SQLiteCommand(Con))
{
    cmd1.CommandText = "PRAGMA foreign_keys = 0"; //turn foreign key contraint off before transaction
    cmd1.ExecuteNonQuery();                      
}

var cmd2 = new SQLiteCommand(Con);
using (SQLiteTransaction transaction = Con.BeginTransaction())
{
    //run update commands here

    transaction.Commit();                       //run transaction
}

using (var cmd3 = new SQLiteCommand(Con))
{
    cmd3.CommandText = "PRAGMA foreign_keys = 1"; //turn foreign key constraint back on after transaction
    cmd3.ExecuteNonQuery();                      //this doesnt work
}

Answer 1

You can have foreign keys enabled during transactions. You just can't turn them on or off during transactions - you have to turn them on before starting the transaction.

It's probably easiest to turn on the pragma when you open the connection - and then you can forget about it.

This works fine for me:

// Con is a SQLite connection, that has been opened.    
using (var cmd = Con.CreateCommand())
{
    cmd.CommandText = "PRAGMA foreign_keys = ON";
    cmd.ExecuteNonQuery();
}

using (var cmd = Con.CreateCommand())
{
    cmd.CommandText = @"
    CREATE TABLE A(id integer not null primary key);
    CREATE TABLE B(id integer not null primary key, a_id integer, FOREIGN KEY(a_id) REFERENCES A(id))
    ";
    cmd.ExecuteNonQuery();
}

try
{
    using (var transaction = Con.BeginTransaction())
    {
        using (var cmd = Con.CreateCommand())
        {
            cmd.CommandText = "INSERT INTO A VALUES(1); INSERT INTO A VALUES(2); INSERT INTO B VALUES(1, NULL); INSERT INTO B VALUES(2, 1);";
            cmd.ExecuteNonQuery();
        }

        using (var cmd = Con.CreateCommand())
        {
            cmd.CommandText = "INSERT INTO B VALUES(3, 55);";
            cmd.ExecuteNonQuery();  //Will crash beceause of invalid reference
        }

        transaction.Commit();
    }
} catch(DbException) {
    // Ignore transaction failure in this example   
    if(!e.Message.Contains("foreign key constraint failed"))
        throw;
}

using (var cmd = Con.CreateCommand())
{
    // Validate that nothing was inserted in the failed transaction
    cmd.CommandText = "SELECT COUNT(*) FROM A";
    Debug.Assert((long)cmd.ExecuteScalar() == 0);
}