sqlalchemy: create relations but without foreign key constraint in db?

Question

Since sqlalchemy.orm.relationship() already implies the relation, and I do not want to create a constraint in db. What should I do?

Currently I manually remove these constraints after alembic migrations.

Answer 1

Instead of defining "schema" level ForeignKey constraints create a custom foreign condition; pass what columns you'd like to use as "foreign keys" and the primaryjoin to relationship. You have to manually define the primaryjoin because:

By default, this value is computed based on the foreign key relationships of the parent and child tables (or association table).

In [2]: class A(Base):    ...:     a_id = Column(Integer, primary_key=True)    ...:     __tablename__ = 'a'    ...:       In [3]: class C(Base):    ...:     c_id = Column(Integer, primary_key=True)    ...:     a_id = Column(Integer)    ...:     __tablename__ = 'c'    ...:     a = relationship('A', foreign_keys=[a_id],    ...:                      primaryjoin='A.a_id == C.a_id')    ...:      

Foreign keys can also be annotated inline in the primaryjoin using foreign():

a = relationship('A', primaryjoin='foreign(C.a_id) == A.a_id') 

You can verify that no FOREIGN KEY constraints are emitted for table c:

In [4]: from sqlalchemy.schema import CreateTable  In [5]: print(CreateTable(A.__table__))  CREATE TABLE a (         a_id INTEGER NOT NULL,          PRIMARY KEY (a_id) )    In [6]: print(CreateTable(C.__table__))  CREATE TABLE c (         c_id INTEGER NOT NULL,          a_id INTEGER,          PRIMARY KEY (c_id) ) 

Warning:

Note that without a FOREIGN KEY constraint in place on the DB side you can blow your referential integrity to pieces any which way you want. There's a relationship at the ORM/application level, but it cannot be enforced in the DB.