Foreign key support in SQLite and Python

Foreign key support was added in SQLite version 3.6.19, Oct 14th 2009 (Release History of SQLite). There is no point trying to use this feature if you have an earlier version of SQLite. But which version do you have? Here are the relevant commands. NB version is the version of pysqlite, not sqlite. So 2.6.0 does not refer to the SQLite version, which is 3.7.4 i.e. very up-to-date as at the time of posting.


>>> import sqlite3 as sqlite
>>> sqlite.version
'2.6.0'
>>> sqlite.sqlite_version
'3.7.4'

The default installation of Python 2.6.6 (which I had to stay with for various reasons) installed an older version of SQLite. But this was easily remedied by installing a newer version (2.7.2) alongside 2.6.6 and overwriting the older version of sqlite3.dll in the C:\Python26\DLL folder with the version from C:\Python27\DLL. It was as simple as that.

Now foreign key constraints are disabled by default, so they must be enabled for each connection.

cur.execute("PRAGMA foreign_keys = ON")

Now to check that it is set, run the following:

cur.execute("PRAGMA foreign_keys")

to run the PRAGMA command you need, and

cur.fetchone()

to get the result back from it. Either (0,) or (1,). NB to do the fetchone() after “PRAGMA foreign_keys” not “PRAGMA foreign_keys = ON”. If nothing is returned, you either forgot the previous instruction and didn’t rerun “PRAGMA foreign_keys” before the fetchone() 😉 or SQLite was compiled with the wrong flags set.

Tip: If the command “PRAGMA foreign_keys” returns no data instead of a single row containing “0” or “1”, then the version of SQLite you are using does not support foreign keys (either because it is older than 3.6.19 or because it was compiled with SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER defined).
SQLite Foreign Key Support

See also:

SQLITE_OMIT_TRIGGER

Defining this option omits support for TRIGGER objects. Neither the CREATE TRIGGER or DROP TRIGGER commands are available in this case, and attempting to execute either will result in a parse error. This option also disables enforcement of foreign key constraints, since the code that implements triggers and which is omitted by this option is also used to implement foreign key actions.
Compilation Options for SQLite

If you are using SQLite as installed along with Python the most likely explanation for no result is that you made a mistake.