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.

You may also have to define the relation using the `relation` factory. — Keith, May 23 '11 at 09:51
@sayap using SQLite, @Keith thanks for the lead, I'll give it a go — mrmagooey, May 23 '11 at 22:50
Looks like it may be an issue with sqlite. I didn't realise but apparently sqlite doesn't support foreign key constraints... or at least didn't, [the newer bundles apparently do](http://stackoverflow.com/q/3296040/599251). I'll install a new version and see what happens — mrmagooey, May 24 '11 at 00:50

score 4 · Accepted Answer · edited May 23 '17 at 10:27

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.

Foreign key constraints in SQLAlchemy

1 Answers1