Why aren't my sqlite3 foreign keys working?
Question: I run the following code from a python interpreter, and expect the insert
statement to fail and throw some kind of exception. But it's not happening:

    
    
    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 doesn't want to work? My understanding is that the
insert should fail since the value I gave for `t2(i)` isn't a primary key in
`t1`, but it happily does it anyway...?


Answer: Working foreign key support in SQLite is very new -- it was only released in
3.6.19 on October 14th. Are you sure you're using SQLite 3.6.19 or later?

Check the sqlite_version constant in the sqlite3 module. E.g. on a Mac OS X
10.6 system with the default python/sqlite install:

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

