Foreign key constraints in SQLAlchemy

Question

I'm using the ORM side of SQLAlchemy, and I've defined one of my columns to have a foreign key relation to another model, using:

Base = declarative_base()
class Model1(Base):
    __tablename__ = 'm1'
    Name = Column(String, primary_key = True)
    info = Column(String)

class Model2(Base):
    __tablename__ = 'm2'
    Name = Column(String, primary_key = True)
    info = Column(String)
    other_model = Column(String, ForeignKey('m1.Name'))

However, it doesn't seem to matter what I put in the other_model attribute, it seems more than happy to commit it to the database, even if there is no Model1 instance that has that Name.

Answer 1

It looks like the answer was in the database I was using (SQLite), not SQLAlchemy. SQLite versions <3.6.1 (AFAIK) do not support foreign key constraints.

The answer is therefore very similar to this answer on foreign keys and SQLAlchemy.

As I'm using Windows, I was able to go to the pysqlite2 page, the packaged installers have version 3.7.6.2 sqlite, and then and the final implementation was aided by this SQLAlchemy page on sqlite engines and dialects. This SO question is also relevant with regards to the upgrade process.

Finally, the SQLite engine is a bit temperamental when deciding whether or not to enforce the foreign key constraint, and this SO question is quite useful in forcing the foreign key enforcement.