invalid values accepted for insert despite ForeignKey

Question

In the below code why is there no error when inserting into table t1? Column b in t1 is a foreign key, so it should only accept values from column c in t2, but somehow I can insert 'bar' without an error. What am I missing here?

from sqlalchemy import create_engine, MetaData, Table, Column, Unicode, ForeignKey

engine = create_engine(r'sqlite:///XXXXXXX.db', echo=True)
metadata = MetaData()
t1 = Table('t1', metadata,
           Column('a', Unicode(), primary_key=True),
           Column('b', Unicode(), ForeignKey('t2.c')))
t2 = Table('t2', metadata,
           Column('c', Unicode(), primary_key=True))
metadata.create_all(engine)
conn = engine.connect()
conn.execute(t2.insert().values(c='first'))
conn.execute(t2.insert().values(c='second'))
conn.execute(t1.insert().values(a='foo', b='bar'))

EDIT

I don't think this question should be marked as a duplicate of the linked one. The linked question is about how to enforce Foreign Key assuming you already know it is not on by default. In my question I observe a strange behaviour (violation of Foreign Key constraint) and ask for a root cause.

Check that you have [enabled foreign key support](https://sqlite.org/foreignkeys.html), just in case. — Ilja Everilä, Oct 27 '17 at 13:04
And read the [SQLA docs on the subject](http://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#foreign-key-support). There are some prerequisites and you must emit `PRAGMA foreign_keys=ON` on each new connection before use. — Ilja Everilä, Oct 27 '17 at 13:18
Thanks, I copied part of the code from SQLA docs, and to my surprise it worked like a charm. I say to my surprise, because I'm beginner in python and I never used events before. The part I don't really understand is how does the set_sqlite_pragma function works. What are it's arguments and where do they come from? — MJB, Oct 27 '17 at 13:38
Are you familiar with how Python decorators work in general? The decorated function is passed to the decorator, as if called like `set_sqlite_pragma = event.listens_for(set_sqlite_pragma)` after the function definition. The `event.listens_for` decorator registers the decorated function as an event listener, which is then called when the event is fired by SQLA. — Ilja Everilä, Oct 27 '17 at 14:09

score 4 · Accepted Answer · answered Oct 27 '17 at 13:32

Thanks Ilja for a push in the right direction. I modified the code in the following way and now it's working as expected, that is raising IntegrityError on the last line.

from sqlalchemy import create_engine, MetaData, Table, Column, Unicode, ForeignKey
from sqlalchemy.engine import Engine
from sqlalchemy import event


@event.listens_for(Engine, "connect")
def set_sqlite_pragma(dbapi_connection, connection_record):
    cursor = dbapi_connection.cursor()
    cursor.execute("PRAGMA foreign_keys=ON")
    cursor.close()

engine = create_engine(r'sqlite:///XXXXXXX.db', echo=True)
metadata = MetaData()
t1 = Table('t1', metadata,
           Column('a', Unicode(), primary_key=True),
           Column('b', Unicode(), ForeignKey('t2.c')))
t2 = Table('t2', metadata,
           Column('c', Unicode(), primary_key=True))
metadata.create_all(engine)
conn = engine.connect()
conn.execute(t2.insert().values(c='first'))
conn.execute(t2.insert().values(c='second'))
conn.execute(t1.insert().values(a='foo', b='bar'))

invalid values accepted for insert despite ForeignKey

1 Answers1