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.