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

Solution 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.