Enabling Foreign key constraints in SQLite

Question

I'm using SQLite with C# and have some tables with foreign keys defined.

Now, I know that by default foreign key constraints are not enforced in SQLite, but I'd like to turn them ON.

Is it possible to do this through code? I have looked up a related question, but I'm not sure how to do it through C# code. I'm using the latest plug-in of SQLite available for Visual Studio 2008 for designing my tables.

conn.Open(); SQLiteCommand cmd = new SQLiteCommand("PRAGMA foreign_keys = ON", conn); cmd.ExecuteNonQuery();  conn.Close(); 

I need this change to persist when this connection is reopened. Is it possible?

Answer 1

Finally figured this out from this post. The PRAGMA foreign_key setting does not persist but you can set it every time the connection is made in the ConnectionString. This allows you to use Visual Studio's table adapters.

1. Make sure you have the latest version (1.0.73.0) of system.data.sqlite installed (1.0.66.0 will not work).
2. Change your ConnectionString to data source=C:\Dbs\myDb.db;foreign keys=true; (replace C:\Dbs\myDb.db with your sqlite database).