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?

Community · Accepted Answer · 2017-05-23 12:34:41Z

73

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, 2017 at 12:34

CommunityBot

11 silver badge

answered Jun 21, 2011 at 1:05

Jer K

9058 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

patentfox Over a year ago

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

Dieter Menne Over a year ago

I tried all other methods mentioned in this post, and only this one worked for me.

Clint Pachl Over a year ago

I am shocked that in 2020 SQLite still disables foreign key constraints, even after explicitly enabling them! Talk about major data corruption.

Marcelo Cantos · Accepted Answer · 2010-11-23 09:17:57Z

9

Turn on the pragma:

PRAGMA foreign_keys = ON;

You can execute this just like any other SQL statement.

answered Nov 23, 2010 at 9:17

Marcelo Cantos

187k40 gold badges338 silver badges366 bronze badges

4 Comments

patentfox Over a year ago

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?

Marcelo Cantos Over a year ago

@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?

patentfox Over a year ago

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?

Marcelo Cantos Over a year ago

@kaustubh: I meant that you should create a SQLiteConnection, invoke the pragma statement on it, and then use the connection to construct the SQLiteDataAdapter.

Bruno Croys Felthes · Accepted Answer · 2013-01-30 21:08:06Z

5

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.

edited Jan 30, 2013 at 21:08

Bruno Croys Felthes

1,2139 silver badges29 bronze badges

answered Jan 30, 2013 at 20:28

Nigel

511 silver badge1 bronze badge

Comments

BenMorel · Accepted Answer · 2013-12-09 16:48:39Z

3

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.

edited Dec 9, 2013 at 16:48

BenMorel

37k52 gold badges208 silver badges339 bronze badges

answered Dec 21, 2011 at 13:36

Chris Aelbrecht

2,09121 silver badges26 bronze badges

Comments

tdammers · Accepted Answer · 2010-11-23 09:22:03Z

2

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

answered Nov 23, 2010 at 9:22

tdammers

20.7k1 gold badge41 silver badges57 bronze badges

1 Comment

patentfox Over a year ago

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?

abatishchev · Accepted Answer · 2010-11-23 09:59:24Z

1

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

answered Nov 23, 2010 at 9:59

abatishchev

101k88 gold badges303 silver badges443 bronze badges

3 Comments

patentfox Over a year ago

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

abatishchev Over a year ago

@kaustubh: Try to use dbFacade. Maybe it supports that? Author's blog unfortunately is in Russian (he is from Ukraine, seems to be), but I can try to help you to translate

abatishchev Over a year ago

@kaustubh: btw, have you added EnforceFKConstraints=Yes or EnforceFKConstraints=True or EnforceFKConstraints=1. Or all of them at once?

pinkfloydx33 · Accepted Answer · 2010-11-23 09:19:29Z

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, 2010 at 9:19

pinkfloydx33

13k3 gold badges54 silver badges70 bronze badges

Comments

Anish.A.R · Accepted Answer · 2013-02-01 13:14:28Z

-2

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

answered Feb 1, 2013 at 13:14

Anish.A.R

221 bronze badge

Collectives™ on Stack Overflow

Enabling Foreign key constraints in SQLite

8 Answers 8

3 Comments

4 Comments

Comments

Comments

1 Comment

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

3 Comments

4 Comments

Comments

Comments

1 Comment

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related