Why are my sqlite3 foreign keys not working?

Question

I am running the following code from the python interpreter and expect the insert statement to fail and throw some kind of exception. But this does not happen:

Python 2.6.5 (r265:79096, Mar 19 2010, 21:48:26) [MSC v.1500 32 bit (Intel)] on win32 Type "help", "copyright", "credits" or "license" for more information. >>> import sqlite3 >>> conn = sqlite3.connect("test.db") >>> conn.executescript(""" ... pragma foreign_keys=on; ... begin transaction; ... create table t1 (i integer primary key, a); ... create table t2 (i, a, foreign key (i) references t1(i)); ... commit; ... """) <sqlite3.Cursor object at 0x0229DAA0> >>> c = conn.cursor() >>> c.execute("insert into t2 values (6, 8)") <sqlite3.Cursor object at 0x0229DAD0> >>> #??? ... >>> conn.commit() >>> #??????????? ... >>> c.execute("select * from t2") <sqlite3.Cursor object at 0x0229DAD0> >>> c.fetchall() [(6, 8)] >>> #but why!? ... >>> 

Does anyone know why this does not want to work? I understand that the insertion should fail, because the value that I gave for t2(i) is not the primary key in t1 , but, fortunately, is it anyway ...?

Answer 1

Support for foreign key in SQLite is very new - it was released only on October 3, 3.6.19. Are you sure you are using SQLite 3.6.19 or later?

Check the sqlite_version constant in the sqlite3 module. For example. on Mac OS X 10.6 with python / sqlite installed by default:

 >>> import sqlite3 >>> sqlite3.sqlite_version '3.6.12' >>> 

Answer 2

As Nicholas said, check if your version of sqlite supports external support. It does not matter if the sqlite version is greater than or equal to 3.6.19. The source can be compiled with foreign key support disabled. To verify, run the following command.

cursor.execute ("PRAGMA foreign_keys")

If it does not return any data, your version does not support a foreign key.

NB: Foreign key support is not currently applicable in sqlite3. Check here .