Enabling Foreign key constraints in SQLite
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.
- Make sure you have the latest version (1.0.73.0) of system.data.sqlite installed (1.0.66.0 will not work).
- Change your ConnectionString to
data source=C:\Dbs\myDb.db;foreign keys=true;(replace C:\Dbs\myDb.db with your sqlite database).
Turn on the pragma:
PRAGMA foreign_keys = ON;
You can execute this just like any other SQL statement.
I too struggled with this issue. I decided to investigate the full connection string generated in SQLDriverConnect() when connecting to the database. This is what it returned:
'Driver={SQLite3 ODBC Driver};Database=C:\Users\Staples\Documents\SQLLiteTest.s3db;StepAPI=;SyncPragma=;NoTXN=;Timeout=;ShortNames=;LongNames=;NoCreat=;NoWCHAR=;FKSupport=;JournalMode=;OEMCP=;LoadExt=;BigInt=;PWD='
As you can see there is a FKSupport property.
After adding FKSupport=True; to my connection string it returned this:
'Driver={SQLite3 ODBCDriver};Database=C:\Users\Staples\Documents\SQLLiteTest.s3db;StepAPI=;SyncPragma=;NoTXN=;Timeout=;ShortNames=;LongNames=;NoCreat=;NoWCHAR=;FKSupport=True;JournalMode=;OEMCP=;LoadExt=;BigInt=;PWD='
And voila! foreign key contraints are enforced.