diff options
Diffstat (limited to 'doc')
| -rw-r--r-- | doc/build/changelog/changelog_10.rst | 18 | ||||
| -rw-r--r-- | doc/build/orm/relationships.rst | 135 |
2 files changed, 149 insertions, 4 deletions
diff --git a/doc/build/changelog/changelog_10.rst b/doc/build/changelog/changelog_10.rst index 5aed3bddd..4454dd98a 100644 --- a/doc/build/changelog/changelog_10.rst +++ b/doc/build/changelog/changelog_10.rst @@ -22,6 +22,24 @@ on compatibility concerns, see :doc:`/changelog/migration_10`. .. change:: + :tags: bug, orm + :tickets: 3230 + + A warning is emitted in the case of multiple relationships that + ultimately will populate a foreign key column in conflict with + another, where the relationships are attempting to copy values + from different source columns. This occurs in the case where + composite foreign keys with overlapping columns are mapped to + relationships that each refer to a different referenced column. + A new documentation section illustrates the example as well as how + to overcome the issue by specifying "foreign" columns specifically + on a per-relationship basis. + + .. seealso:: + + :ref:`relationship_overlapping_foreignkeys` + + .. change:: :tags: feature, sql :tickets: 3172 diff --git a/doc/build/orm/relationships.rst b/doc/build/orm/relationships.rst index c65f06cbc..f512251a7 100644 --- a/doc/build/orm/relationships.rst +++ b/doc/build/orm/relationships.rst @@ -1079,12 +1079,15 @@ The above relationship will produce a join like:: ON host_entry_1.ip_address = CAST(host_entry.content AS INET) An alternative syntax to the above is to use the :func:`.foreign` and -:func:`.remote` :term:`annotations`, inline within the :paramref:`~.relationship.primaryjoin` expression. +:func:`.remote` :term:`annotations`, +inline within the :paramref:`~.relationship.primaryjoin` expression. This syntax represents the annotations that :func:`.relationship` normally applies by itself to the join condition given the :paramref:`~.relationship.foreign_keys` and -:paramref:`~.relationship.remote_side` arguments; the functions are provided in the API in the -rare case that :func:`.relationship` can't determine the exact location -of these features on its own:: +:paramref:`~.relationship.remote_side` arguments. These functions may +be more succinct when an explicit join condition is present, and additionally +serve to mark exactly the column that is "foreign" or "remote" independent +of whether that column is stated multiple times or within complex +SQL expressions:: from sqlalchemy.orm import foreign, remote @@ -1157,6 +1160,130 @@ Will render as:: flag to assist in the creation of :func:`.relationship` constructs using custom operators. +.. _relationship_overlapping_foreignkeys: + +Overlapping Foreign Keys +~~~~~~~~~~~~~~~~~~~~~~~~ + +A rare scenario can arise when composite foreign keys are used, such that +a single column may be the subject of more than one column +referred to via foreign key constraint. + +Consider an (admittedly complex) mapping such as the ``Magazine`` object, +referred to both by the ``Writer`` object and the ``Article`` object +using a composite primary key scheme that includes ``magazine_id`` +for both; then to make ``Article`` refer to ``Writer`` as well, +``Article.magazine_id`` is involved in two separate relationships; +``Article.magazine`` and ``Article.writer``:: + + class Magazine(Base): + __tablename__ = 'magazine' + + id = Column(Integer, primary_key=True) + + + class Article(Base): + __tablename__ = 'article' + + article_id = Column(Integer) + magazine_id = Column(ForeignKey('magazine.id')) + writer_id = Column() + + magazine = relationship("Magazine") + writer = relationship("Writer") + + __table_args__ = ( + PrimaryKeyConstraint('article_id', 'magazine_id'), + ForeignKeyConstraint( + ['writer_id', 'magazine_id'], + ['writer.id', 'writer.magazine_id'] + ), + ) + + + class Writer(Base): + __tablename__ = 'writer' + + id = Column(Integer, primary_key=True) + magazine_id = Column(ForeignKey('magazine.id'), primary_key=True) + magazine = relationship("Magazine") + +When the above mapping is configured, we will see this warning emitted:: + + SAWarning: relationship 'Article.writer' will copy column + writer.magazine_id to column article.magazine_id, + which conflicts with relationship(s): 'Article.magazine' + (copies magazine.id to article.magazine_id). Consider applying + viewonly=True to read-only relationships, or provide a primaryjoin + condition marking writable columns with the foreign() annotation. + +What this refers to originates from the fact that ``Article.magazine_id`` is +the subject of two different foreign key constraints; it refers to +``Magazine.id`` directly as a source column, but also refers to +``Writer.magazine_id`` as a source column in the context of the +composite key to ``Writer``. If we associate an ``Article`` with a +particular ``Magazine``, but then associate the ``Article`` with a +``Writer`` that's associated with a *different* ``Magazine``, the ORM +will overwrite ``Article.magazine_id`` non-deterministically, silently +changing which magazine we refer towards; it may +also attempt to place NULL into this columnn if we de-associate a +``Writer`` from an ``Article``. The warning lets us know this is the case. + +To solve this, we need to break out the behavior of ``Article`` to include +all three of the following features: + +1. ``Article`` first and foremost writes to + ``Article.magazine_id`` based on data persisted in the ``Article.magazine`` + relationship only, that is a value copied from ``Magazine.id``. + +2. ``Article`` can write to ``Article.writer_id`` on behalf of data + persisted in the ``Article.writer`` relationship, but only the + ``Writer.id`` column; the ``Writer.magazine_id`` column should not + be written into ``Article.magazine_id`` as it ultimately is sourced + from ``Magazine.id``. + +3. ``Article`` takes ``Article.magazine_id`` into account when loading + ``Article.writer``, even though it *doesn't* write to it on behalf + of this relationship. + +To get just #1 and #2, we could specify only ``Article.writer_id`` as the +"foreign keys" for ``Article.writer``:: + + class Article(Base): + # ... + + writer = relationship("Writer", foreign_keys='Article.writer_id') + +However, this has the effect of ``Article.writer`` not taking +``Article.magazine_id`` into account when querying against ``Writer``: + +.. sourcecode:: sql + + SELECT article.article_id AS article_article_id, + article.magazine_id AS article_magazine_id, + article.writer_id AS article_writer_id + FROM article + JOIN writer ON writer.id = article.writer_id + +Therefore, to get at all of #1, #2, and #3, we express the join condition +as well as which columns to be written by combining +:paramref:`~.relationship.primaryjoin` fully, along with either the +:paramref:`~.relationship.foreign_keys` argument, or more succinctly by +annotating with :func:`~.orm.foreign`:: + + class Article(Base): + # ... + + writer = relationship( + "Writer", + primaryjoin="and_(Writer.id == foreign(Article.writer_id), " + "Writer.magazine_id == Article.magazine_id)") + +.. versionchanged:: 1.0.0 the ORM will attempt to warn when a column is used + as the synchronization target from more than one relationship + simultaneously. + + Non-relational Comparisons / Materialized Path ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ |