Why (and how ?) does sqlalchemy bypass FK constrainsts with sqlite?

Question

While I was doing unit testing, I wrote some code that I knew wouldn't pass because of foreign constrainst... Except it did.

This is an example :

import os
import sqlalchemy as sa
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base, declared_attr
import logging
logging.basicConfig()
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)

try:
  os.unlink('test.db')
except:
  pass
engine = create_engine('sqlite:///test.db')

Base = declarative_base()

class Test1(Base):
  __tablename__ = 'Test1'
  id = sa.Column(sa.Integer(), primary_key = True)

class Test2(Base):
  __tablename__ = 'Test2'
  id = sa.Column(sa.Integer(), primary_key = True)
  link = sa.Column(sa.Integer(), sa.ForeignKey(Test1.id), index = True)

Base.metadata.create_all(engine)

Session = sessionmaker()

session = Session(bind=engine)

session.add(Test2(id=1, link=2)) # <-- should raise an error because 2 not in Test1
session.commit()
session.add(Test1(id=1))
session.commit()
session.add(Test2(id=2, link=1)) #ok
session.commit()
session.add(Test2(id=3, link=42)) # <-- should raise an error because 42 not in Test1
session.commit()

I declare a simple database, a table Test1 with a PK id, and a second table Test2 with a PK id and a FK constrainst on Test2.link to reference Test1.id. Then, I insert some values that would break the FK.

The FK seems to work because if I open a sqlite db browser, I can't put these same values...

Why and how sqlalchemy bypasses the FK ?

Note : I haven't tried on another db

Answer 1

Did you turn on enforcement in SQLite?

> PRAGMA foreign_keys = ON;

See related question. To enable this in code, one can use

engine = create_engine(database_url)
engine.execute('PRAGMA foreign_keys=ON')

This other answer has an example for using listeners/events to run this on connect.

def _fk_pragma_on_connect(dbapi_con, con_record):
    dbapi_con.execute('PRAGMA foreign_keys=ON')

from sqlalchemy import event
event.listen(engine, 'connect', _fk_pragma_on_connect)