Паттерны устойчивости особых отношений¶

Строки, указывающие сами на себя / Взаимозависимые строки¶

Это очень специфический случай, когда relationship() должен выполнить INSERT и второй UPDATE, чтобы правильно заполнить строку (и наоборот, UPDATE и DELETE, чтобы удалить без нарушения ограничений внешнего ключа). Два варианта использования:

• Таблица содержит внешний ключ к самой себе, и отдельная строка будет иметь значение внешнего ключа, указывающее на ее собственный первичный ключ.

• Две таблицы содержат внешний ключ, ссылающийся на другую таблицу, при этом строка в каждой таблице ссылается на другую.

Например:

          user
---------------------------------
user_id    name   related_user_id
   1       'ed'          1

Или:

             widget                                                  entry
-------------------------------------------             ---------------------------------
widget_id     name        favorite_entry_id             entry_id      name      widget_id
   1       'somewidget'          5                         5       'someentry'     1

В первом случае строка указывает на саму себя. Технически, база данных, использующая последовательности, например, PostgreSQL или Oracle, может сразу вставить строку, используя ранее созданное значение, но базы данных, которые полагаются на идентификаторы первичного ключа в стиле автоинкремента, не могут. relationship() всегда предполагает модель «родитель/потомок» заполнения строки во время промывки, поэтому, если вы не заполняете столбцы первичного ключа/внешнего ключа напрямую, relationship() должен использовать два оператора.

Во втором случае строка «widget» должна быть вставлена до всех ссылающихся строк «entry», но тогда столбец «favorite_entry_id» этой строки «widget» не может быть установлен, пока не будут созданы строки «entry». В этом случае, как правило, невозможно вставить строки «виджет» и «запись» с помощью только двух операторов INSERT; необходимо выполнить UPDATE, чтобы сохранить ограничения внешних ключей. Исключение составляют случаи, когда внешние ключи настроены как «отложенные до фиксации» (эта функция поддерживается некоторыми базами данных) и если идентификаторы были заполнены вручную (опять же в обход relationship()).

Чтобы разрешить использование дополнительного оператора UPDATE, мы используем опцию relationship.post_update в relationship(). Это указывает, что связь между двумя строками должна быть создана с помощью оператора UPDATE после того, как обе строки были INSERTED; это также заставляет строки быть деассоциированными друг с другом с помощью UPDATE до того, как будет выполнено DELETE. Флаг должен быть установлен только на одном из отношений, предпочтительно на стороне «многие-к-одному». Ниже приведен полный пример, включающий две конструкции ForeignKey:

from sqlalchemy import Integer, ForeignKey, Column
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

Base = declarative_base()


class Entry(Base):
    __tablename__ = "entry"
    entry_id = Column(Integer, primary_key=True)
    widget_id = Column(Integer, ForeignKey("widget.widget_id"))
    name = Column(String(50))


class Widget(Base):
    __tablename__ = "widget"

    widget_id = Column(Integer, primary_key=True)
    favorite_entry_id = Column(
        Integer, ForeignKey("entry.entry_id", name="fk_favorite_entry")
    )
    name = Column(String(50))

    entries = relationship(Entry, primaryjoin=widget_id == Entry.widget_id)
    favorite_entry = relationship(
        Entry, primaryjoin=favorite_entry_id == Entry.entry_id, post_update=True
    )

Когда структура с приведенной выше конфигурацией промывается, строка «widget» будет INSERTed минус значение «favorite_entry_id», затем все строки «entry» будут INSERTed со ссылкой на родительскую строку «widget», а затем оператор UPDATE заполнит столбец «favorite_entry_id» таблицы «widget» (пока что это одна строка за раз):

>>> w1 = Widget(name="somewidget")
>>> e1 = Entry(name="someentry")
>>> w1.favorite_entry = e1
>>> w1.entries = [e1]
>>> session.add_all([w1, e1])
sql>>> session.commit()
BEGIN (implicit)
INSERT INTO widget (favorite_entry_id, name) VALUES (?, ?)
(None, 'somewidget')
INSERT INTO entry (widget_id, name) VALUES (?, ?)
(1, 'someentry')
UPDATE widget SET favorite_entry_id=? WHERE widget.widget_id = ?
(1, 1)
COMMIT

Дополнительная конфигурация, которую мы можем задать, заключается в предоставлении более полного ограничения внешнего ключа на Widget, такого, что гарантируется, что favorite_entry_id ссылается на Entry, который также ссылается на этот Widget. Мы можем использовать составной внешний ключ, как показано ниже:

from sqlalchemy import (
    Integer,
    ForeignKey,
    String,
    Column,
    UniqueConstraint,
    ForeignKeyConstraint,
)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

Base = declarative_base()


class Entry(Base):
    __tablename__ = "entry"
    entry_id = Column(Integer, primary_key=True)
    widget_id = Column(Integer, ForeignKey("widget.widget_id"))
    name = Column(String(50))
    __table_args__ = (UniqueConstraint("entry_id", "widget_id"),)


class Widget(Base):
    __tablename__ = "widget"

    widget_id = Column(Integer, autoincrement="ignore_fk", primary_key=True)
    favorite_entry_id = Column(Integer)

    name = Column(String(50))

    __table_args__ = (
        ForeignKeyConstraint(
            ["widget_id", "favorite_entry_id"],
            ["entry.widget_id", "entry.entry_id"],
            name="fk_favorite_entry",
        ),
    )

    entries = relationship(
        Entry, primaryjoin=widget_id == Entry.widget_id, foreign_keys=Entry.widget_id
    )
    favorite_entry = relationship(
        Entry,
        primaryjoin=favorite_entry_id == Entry.entry_id,
        foreign_keys=favorite_entry_id,
        post_update=True,
    )

Приведенное выше отображение имеет составной ForeignKeyConstraint, соединяющий столбцы widget_id и favorite_entry_id. Чтобы убедиться, что Widget.widget_id остается «автоинкрементным» столбцом, мы указываем Column.autoincrement на значение "ignore_fk" на Column, и дополнительно на каждом relationship() мы должны ограничить столбцы, рассматриваемые как часть внешнего ключа для целей объединения и перекрестного заполнения.

Изменяемые первичные ключи / Каскады обновлений¶

Когда первичный ключ сущности изменяется, связанные элементы, которые ссылаются на первичный ключ, также должны быть обновлены. Для баз данных, обеспечивающих ссылочную целостность, лучшей стратегией является использование функции ON UPDATE CASCADE базы данных для распространения изменений первичного ключа на ссылающиеся внешние ключи - значения не могут быть рассинхронизированы в любой момент, если только ограничения не помечены как «откладываемые», то есть не выполняются до завершения транзакции.

Настоятельно рекомендуется, чтобы приложение, которое стремится использовать естественные первичные ключи с изменяемыми значениями, использовало возможности базы данных ON UPDATE CASCADE. Примером отображения, иллюстрирующим это, является:

class User(Base):
    __tablename__ = "user"
    __table_args__ = {"mysql_engine": "InnoDB"}

    username = Column(String(50), primary_key=True)
    fullname = Column(String(100))

    addresses = relationship("Address")


class Address(Base):
    __tablename__ = "address"
    __table_args__ = {"mysql_engine": "InnoDB"}

    email = Column(String(50), primary_key=True)
    username = Column(String(50), ForeignKey("user.username", onupdate="cascade"))

Выше мы проиллюстрировали onupdate="cascade" на объекте ForeignKey, а также настройку mysql_engine='InnoDB', которая на бэкенде MySQL гарантирует, что используется механизм InnoDB, поддерживающий ссылочную целостность. При использовании SQLite ссылочная целостность должна быть включена, используя конфигурацию, описанную в sqlite_foreign_keys.

class User(Base):
    __tablename__ = "user"

    username = Column(String(50), primary_key=True)
    fullname = Column(String(100))

    # passive_updates=False *only* needed if the database
    # does not implement ON UPDATE CASCADE
    addresses = relationship("Address", passive_updates=False)


class Address(Base):
    __tablename__ = "address"

    email = Column(String(50), primary_key=True)
    username = Column(String(50), ForeignKey("user.username"))