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?

score 72 · Accepted Answer · edited May 23 '17 at 12:34

72

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

edited May 23 '17 at 12:34

Community

1
1

answered Jun 21 '11 at 01:05

Jer K

885
8
11

1

I was using version 1.0.66.0 then. I went ahead without the foreign key feature. Its nice to know that this issue is finally resolved. I'll keep it in mind for any future work. Thanx – patentfox Jun 21 '11 at 04:48
I tried all other methods mentioned in this post, and only this one worked for me. – Dieter Menne Mar 07 '13 at 10:51
4

I am shocked that in 2020 SQLite still disables foreign key constraints, *even after explicitly **enabling** them*! Talk about major data corruption. – Clint Pachl Aug 31 '20 at 07:55

score 6 · Answer 2 · answered Nov 23 '10 at 09:17

6

Turn on the pragma:

PRAGMA foreign_keys = ON;

You can execute this just like any other SQL statement.

answered Nov 23 '10 at 09:17

Marcelo Cantos

181,030
38
327
365

1

I'm using SqliteDataAdapter, it manages opening and closing of connections. I believe when a connection is closed, this information is lost. How can I make this information persist? – patentfox Nov 23 '10 at 09:26
@kaustubh: You are right, the foreign_key pragma is session-local. I don't know how SqliteDataAdapter works, but it seems to take a SQLConnection parameter in its constructor. Could you issue the pragma against the connection when you create it? – Marcelo Cantos Nov 23 '10 at 09:35
There doesn't seem to be a way to specify this information in connection string. Is there some way to specify a command that is executed each time a connection is opened? – patentfox Nov 23 '10 at 09:49
@kaustubh: I meant that you should create a SQLiteConnection, invoke the pragma statement on it, and then use the connection to construct the SQLiteDataAdapter. – Marcelo Cantos Nov 24 '10 at 09:56

score 5 · Answer 3 · edited Jan 30 '13 at 21:08

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.

score 3 · Answer 4 · edited Dec 09 '13 at 16:48

Another solution is to do the "PRAGMA foreign_keys=ON" with every query.

    SQLiteConnection connection = new SQLiteConnection("Data Source=" + dbSQLite + ";Read Only=False;");
    connection.Open();
    SQLiteCommand mycommand = new SQLiteCommand(connection);
    mycommand.CommandText = "PRAGMA foreign_keys=ON";
    mycommand.ExecuteNonQuery();
    mycommand.CommandText = "DELETE FROM table WHERE ID=x";
    mycommand.ExecuteReader();
    connection.Close();

If you put it in a function to which you pass the CommandText you can reuse it.

score 2 · Answer 5 · answered Nov 23 '10 at 09:22

These should provide the information you're looking for:

http://www.sqlite.org/faq.html#q22

http://www.sqlite.org/foreignkeys.html#fk_enable

In short, versions before 3.6.19 don't enforce foreign keys at all, but they can be simulated using triggers; starting with 3.6.19, foreign keys can be enforced, but this needs to be enabled per connection using the PRAGMA foreign_keys = ON statement, and sqlite must be compiled with trigger and foreign key support enabled (which I'd expect to be the case for any binary distribution).

I'm using SqliteDataAdapter which manages opening and closing of connections automatically. How do I, then enforce this PRAGMA each time an Update is made? — patentfox, Nov 23 '10 at 09:32

score 1 · Answer 6 · answered Nov 23 '10 at 09:59

1

Add to your connection string: ";EnforceFKConstraints=Yes|True|1;"

answered Nov 23 '10 at 09:59

abatishchev

98,240
88
296
433

I was euphoric on seeing your reply, seemed like the perfect solution.. But alas, it doesn't work :( Maybe Sqlite data provider for .NET doesn't support it yet.. If it does, then I'm at my wit's end how to make it work.. – patentfox Nov 23 '10 at 10:24
@kaustubh: Try to use [dbFacade](http://krez0n.org.ua/wp-content/uploads/files/dbFacade.zip). Maybe it supports that? [Author's blog](http://krez0n.org.ua/archives/84) unfortunately is in Russian (he is from Ukraine, seems to be), but I can try to help you to translate – abatishchev Nov 23 '10 at 10:36
@kaustubh: btw, have you added `EnforceFKConstraints=Yes` or `EnforceFKConstraints=True` or `EnforceFKConstraints=1`. Or all of them at once? – abatishchev Nov 23 '10 at 10:40

score 0 · Answer 7 · answered Nov 23 '10 at 09:19

0

Looks like you can just execute the SQL command PRAGMA foreign_keys = ON; on your DB connection just like you would a Select or an Update statement. Though you have to make sure your SQLite was compiled with foreign keys and such. See Here.

answered Nov 23 '10 at 09:19

pinkfloydx33

11,863
3
46
63

score -2 · Answer 8 · answered Feb 01 '13 at 13:14

In C++ store app the following code helped me to enable PRAGMA foreign_keys
sqlite3_stmt* stmt;
sqlite3_prepare(db, "PRAGMA foreign_keys = ON;", -1, &stmt, 0); 
sqlite3_step(stmt);

I called this after creating db using the call of 

int rc = sqlite3_open16(path->Data(), &db);

Enabling Foreign key constraints in SQLite

8 Answers8

Linked