I'm misunderstanding something about SqlAlchemy ForeignKey constraints. My understanding is that the insertion of B below should raise a ForeignKeyConstraint exception because there's no A with "my_a" as its name. Isn't that what a ForeignKey constraint does? Require the existence of the value in the table column mapped by the constraint when the constrained table is updated?
from sqlalchemy import Column, create_engine, ForeignKey, Integer, VARCHAR
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
class A(Base):
__tablename__ = 'table_A'
id = Column(Integer, primary_key=True)
name = Column(VARCHAR(32))
class B(Base):
__tablename__ = 'table_B'
id = Column(Integer, primary_key=True)
a_name = Column(VARCHAR(32), ForeignKey('table_A.name'), nullable=False)
engine = create_engine('sqlite:////tmp/AB.db.foo')
Base.metadata.create_all(engine)
Session = sessionmaker()
Session.configure(bind=engine)
b = B(a_name="my_a")
session = Session()
session.add(b)
session.commit()
session.close()
SQLite – even modern versions – does not enforce foreign keys by default.
Assuming the [SQLite] library is compiled with foreign key constraints enabled, it must still be enabled by the application at runtime, using the PRAGMA foreign_keys command.