How to correctly add Foreign Key constraints to SQLite DB using SQLAlchemy [duplicate]
SQLite by default does not enforce ForeignKey constraints (see here http://www.sqlite.org/pragma.html#pragma_foreign_keys )
To enable, follow these docs here: http://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#foreign-key-support
Here's a copy paste of the official documentation:
SQLite supports FOREIGN KEY syntax when emitting CREATE statements for tables, however by default these constraints have no effect on the operation of the table.
Constraint checking on SQLite has three prerequisites:
- At least version 3.6.19 of SQLite must be in use
- The SQLite library must be compiled without the SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER symbols enabled.
- The
PRAGMA foreign_keys = ONstatement must be emitted on all connections before use. SQLAlchemy allows for the PRAGMA statement to be emitted automatically for new connections through the usage of events:
from sqlalchemy.engine import Enginefrom 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()