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

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 CreateTableIn [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.