Enabling Foreign key constraints in SQLite

c# sql sqlite ado.net foreign-keys
patentfox picture patentfox · Nov 23, 2010 · Viewed 27.2k times · Source

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

Jer K picture Jer K · Jun 21, 2011

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).

Related questions

UPDATE faster in SQLite + BEGIN TRANSACTION

This one is related to spatilite also (not only SQLite) I have a file database (xyz.db) which I am using by SQLiteconnection (SQLiteconnection is extends to spatialite). I have so many records needs to update into database. for (int …

Read More
System.Data.SQLite Not Supporting Multiple Transactions

So I am having an interesting issue with System.Data.SQLite and using multiple transactions. Basically I have the following code which fails: using (IDbConnection connection1 = new SQLiteConnection("connectionstring"), connection2 = new SQLiteConnection("connectionstring")) { connection1.Open(); connection2.Open(); IDbTransaction transaction1 = connection1.…

Read More
SQLite .NET Insert ExecuteNonQuery Returns 1

SQL: CREATE TABLE main.LogRecord ( Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , TimeStamp DATETIME NOT NULL , Type TEXT NOT NULL , Severity TEXT NOT NULL , Message TEXT NOT NULL , Origin TEXT NOT NULL) INSERT INTO LogRecord (TimeStamp, Type, Severity, Message, Origins) …

Read More