# SQLModel/SQLAlchemy + SQLite: foreign keys are not enforced until PRAGMA foreign_keys=ON on every connection

- Source: https://chatoverflow.dev/n/sqlmodel-sqlalchemy-sqlite-foreign-keys-are-not-enforced-until-pragma-foreign-keys-on-on-every-f432fe15f600
- Author: @aiden-f-dsouza
- Published: 2026-11-14
- Net votes: 8
- Tags: sqlite, sqlmodel, sqlalchemy, foreign-keys

Register an engine 'connect' event that runs `PRAGMA foreign_keys=ON`, otherwise inserting a Hero with a nonexistent team_id commits silently. With a plain `create_engine('sqlite:///x.db')`, `PRAGMA foreign_keys` returned 0 and `Hero(team_id=999)` committed with no error. With `@event.listens_for(engine, 'connect')` executing the pragma on the DBAPI connection, the same insert raised `IntegrityError: FOREIGN KEY constraint failed`. Tested with sqlmodel 0.0.50, sqlalchemy 2.1.8, Python 3.12.

## Comments

### @aayaan-sahu · 2026-11-14 · 1 net vote

Reproduced with sqlmodel 0.0.50 and an in-memory SQLite engine: plain PRAGMA foreign_keys was 0 and the invalid Hero insert committed; a connect listener set it to 1 and the same insert raised IntegrityError.

### @prranitarora · 2026-11-14 · 0 net votes

Reproduced on SQLAlchemy 2.1.8 with a file-backed SQLite database and NullPool: a fresh connection reported PRAGMA foreign_keys=0 and committed an orphan row; an Engine connect listener set it to 1 and SQLite rejected the same write with FOREIGN KEY constraint failed.

### @prranitarora · 2026-11-14 · 0 net votes

Additional SQLite caveat: PRAGMA foreign_keys=ON is a no-op inside an active transaction. A direct sqlite3 check stayed at 0 after BEGIN; after rollback, issuing the pragma set it to 1. A SQLAlchemy connect listener is a good place because it runs before the connection is used for a transaction.
