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

by

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.

More Related Contents:

  1. Sqlite / SQLAlchemy: how to enforce Foreign Keys?
  2. SQL Alchemy ORM returning a single column, how to avoid common post processing
  3. Efficiently updating database using SQLAlchemy ORM
  4. Why is SQLAlchemy insert with sqlite 25 times slower than using sqlite3 directly?
  5. SQLAlchemy ManyToMany secondary table with additional fields
  6. Bulk insert with SQLAlchemy ORM
  7. How can I verify Column data types in the SQLAlchemy ORM?
  8. SqlAlchemy – Filtering by Relationship Attribute
  9. SQLAlchemy: engine, connection and session difference
  10. SQLAlchemy: how to filter date field?
  11. flask-sqlalchemy or sqlalchemy
  12. How to update sqlalchemy orm object by a python dict
  13. SQLAlchemy: get Model from table name. This may imply appending some function to a metaclass constructor as far as I can see
  14. Is possible to mapping view with class using mapper in SqlAlchemy?
  15. SQLALchemy dynamic filter_by
  16. SQLAlchemy ordering by count on a many to many relationship
  17. Writing a connection string when password contains special characters
  18. Does SQLAlchemy have an equivalent of Django’s get_or_create?
  19. SQLAlchemy: What’s the difference between flush() and commit()?
  20. Flask-SQLalchemy update a row’s information
  21. SQLAlchemy ORDER BY DESCENDING?
  22. SQLAlchemy ON DUPLICATE KEY UPDATE
  23. stored procedures with sqlAlchemy
  24. How do you join two tables on a foreign key field using django ORM?
  25. How to upsert pandas DataFrame to PostgreSQL table?
  26. How can I profile a SQLAlchemy powered application?
  27. pandas to_sql all columns as nvarchar
  28. How to make mysql connection that requires CA-CERT with sqlalchemy or SQLObject
  29. Can’t change system default SQLite binary
  30. add column to SQLAlchemy Table

Leave a Comment