Enabling SQLite Foreign Keys:
SQLite lets create foreign keys but will not enforce them upon insertion or deletion. I spent sometime playing with this and it turns out that there are two ways to make SQLite enforce the foreign key constraint:1) And the this is the easiest way. In the connection string you can specify that foreign keys should be enforce like so (tested using System.Data.SQLite ADO.NET driver):
<add name="ConnectionName" connectionString="Data Source=dbname.db;Version=3;New=True;Foreign Keys=True;" providerName="System.Data.SQLite"/>2) For every connection you can turn on the foreign_keys pragama using the command
PRAGMA foreign_keys = ON; as documented here http://www.sqlite.org/foreignkeys.html#fk_enable
So with the second approach one can do something similar to the following pseudocode:
SQLiteConnection cn = new SQLiteConnection(...);
SQLiteCommend cmd = new SQLiteCommand(cn);
cn.Open();
cmd.CommandText = "PRAGMA foreign_keys = ON;"
cmd.ExecuteNonQuery(); /* so far we ensured that foreign keys constraints will be enforced */
cmd.CommandText = /* some SQL code here */
cmd.Execute ...
I prefer approach 1).
