Определение ограничений и индексов¶

Создание/удаление ограничений внешних ключей с помощью ALTER¶

Поведение, которое мы видели в учебниках и в других местах, связанных с внешними ключами с DDL, показывает, что ограничения обычно отображаются «в линию» в операторе CREATE TABLE, например:

CREATE TABLE addresses (
    id INTEGER NOT NULL,
    user_id INTEGER,
    email_address VARCHAR NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT user_id_fk FOREIGN KEY(user_id) REFERENCES users (id)
)

Директива CONSTRAINT .. FOREIGN KEY используется для создания ограничения «встроенным» способом в определении CREATE TABLE. Методы MetaData.create_all() и MetaData.drop_all() делают это по умолчанию, используя топологическую сортировку всех задействованных объектов Table таким образом, что таблицы создаются и удаляются в порядке зависимости от внешнего ключа (эта сортировка также доступна через аксессор MetaData.sorted_tables).

Этот подход не может работать, когда два или более ограничений по внешнему ключу вовлечены в «цикл зависимости», где набор таблиц взаимно зависит друг от друга, предполагая, что бэкенд принудительно использует внешние ключи (всегда так, кроме SQLite, MySQL/MyISAM). Поэтому методы будут разбивать ограничения в таком цикле на отдельные операторы ALTER, на всех бэкендах, кроме SQLite, который не поддерживает большинство форм ALTER. Дана схема вида:

node = Table(
    "node",
    metadata_obj,
    Column("node_id", Integer, primary_key=True),
    Column("primary_element", Integer, ForeignKey("element.element_id")),
)

element = Table(
    "element",
    metadata_obj,
    Column("element_id", Integer, primary_key=True),
    Column("parent_node_id", Integer),
    ForeignKeyConstraint(
        ["parent_node_id"], ["node.node_id"], name="fk_element_parent_node_id"
    ),
)

Когда мы обращаемся к MetaData.create_all() на бэкенде, таком как бэкенд PostgreSQL, цикл между этими двумя таблицами разрешается, и ограничения создаются отдельно:

>>> with engine.connect() as conn:
...     metadata_obj.create_all(conn, checkfirst=False)
CREATE TABLE element (
    element_id SERIAL NOT NULL,
    parent_node_id INTEGER,
    PRIMARY KEY (element_id)
)

CREATE TABLE node (
    node_id SERIAL NOT NULL,
    primary_element INTEGER,
    PRIMARY KEY (node_id)
)

ALTER TABLE element ADD CONSTRAINT fk_element_parent_node_id
    FOREIGN KEY(parent_node_id) REFERENCES node (node_id)
ALTER TABLE node ADD FOREIGN KEY(primary_element)
    REFERENCES element (element_id)

Для того чтобы выдать DROP для этих таблиц, применяется та же логика, однако обратите внимание, что в SQL для выдачи DROP CONSTRAINT требуется, чтобы ограничение имело имя. В случае с таблицей 'node' выше, мы не назвали это ограничение; поэтому система попытается выдать DROP только для тех ограничений, которые названы:

>>> with engine.connect() as conn:
...     metadata_obj.drop_all(conn, checkfirst=False)
ALTER TABLE element DROP CONSTRAINT fk_element_parent_node_id
DROP TABLE node
DROP TABLE element

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

sqlalchemy.exc.CircularDependencyError: Can't sort tables for DROP;
an unresolvable foreign key dependency exists between tables:
element, node.  Please ensure that the ForeignKey and ForeignKeyConstraint
objects involved in the cycle have names so that they can be dropped
using DROP CONSTRAINT.

Эта ошибка относится только к случаю DROP, поскольку в случае CREATE мы можем выдать «ADD CONSTRAINT» без имени; база данных обычно назначает его автоматически.

Аргументы ключевых слов ForeignKeyConstraint.use_alter и ForeignKey.use_alter могут быть использованы для ручного разрешения циклов зависимости. Мы можем добавить этот флаг только к таблице 'element' следующим образом:

element = Table(
    "element",
    metadata_obj,
    Column("element_id", Integer, primary_key=True),
    Column("parent_node_id", Integer),
    ForeignKeyConstraint(
        ["parent_node_id"],
        ["node.node_id"],
        use_alter=True,
        name="fk_element_parent_node_id",
    ),
)

в нашем CREATE DDL мы увидим оператор ALTER только для этого ограничения, а не для другого:

>>> with engine.connect() as conn:
...     metadata_obj.create_all(conn, checkfirst=False)
CREATE TABLE element (
    element_id SERIAL NOT NULL,
    parent_node_id INTEGER,
    PRIMARY KEY (element_id)
)

CREATE TABLE node (
    node_id SERIAL NOT NULL,
    primary_element INTEGER,
    PRIMARY KEY (node_id),
    FOREIGN KEY(primary_element) REFERENCES element (element_id)
)

ALTER TABLE element ADD CONSTRAINT fk_element_parent_node_id
FOREIGN KEY(parent_node_id) REFERENCES node (node_id)

ForeignKeyConstraint.use_alter и ForeignKey.use_alter, когда они используются в сочетании с операцией drop, требуют, чтобы ограничение было названо, иначе будет выдана ошибка, подобная следующей:

sqlalchemy.exc.CompileError: Can't emit DROP CONSTRAINT for constraint
ForeignKeyConstraint(...); it has no name

ON UPDATE и ON DELETE¶

Большинство баз данных поддерживают каскадирование значений внешних ключей, то есть при обновлении родительской строки новое значение помещается в дочерние строки, или при удалении родительской строки все соответствующие дочерние строки устанавливаются в null или удаляются. На языке определения данных они задаются с помощью фраз типа «ON UPDATE CASCADE», «ON DELETE CASCADE» и «ON DELETE SET NULL», соответствующих ограничениям внешнего ключа. Фраза после «ON UPDATE» или «ON DELETE» может содержать и другие фразы, характерные для используемой базы данных. Объекты ForeignKey и ForeignKeyConstraint поддерживают генерацию этой фразы через аргументы ключевых слов onupdate и ondelete. Значением является любая строка, которая будет выведена после соответствующей фразы «ON UPDATE» или «ON DELETE»:

child = Table(
    "child",
    metadata_obj,
    Column(
        "id",
        Integer,
        ForeignKey("parent.id", onupdate="CASCADE", ondelete="CASCADE"),
        primary_key=True,
    ),
)

composite = Table(
    "composite",
    metadata_obj,
    Column("id", Integer, primary_key=True),
    Column("rev_id", Integer),
    Column("note_id", Integer),
    ForeignKeyConstraint(
        ["rev_id", "note_id"],
        ["revisions.id", "revisions.note_id"],
        onupdate="CASCADE",
        ondelete="SET NULL",
    ),
)

Обратите внимание, что эти пункты требуют InnoDB таблиц при использовании с MySQL. Они также могут не поддерживаться в других базах данных.

Настройка соглашения об именовании для коллекции метаданных¶

MetaData.naming_convention относится к словарю, который принимает класс Index или отдельные классы Constraint в качестве ключей, а шаблоны строк Python в качестве значений. Он также принимает ряд строковых кодов в качестве альтернативных ключей, "fk", "pk", "ix", "ck", "uq" для внешнего ключа, первичного ключа, индекса, проверки и уникального ограничения, соответственно. Строковые шаблоны в этом словаре используются всякий раз, когда с объектом MetaData ассоциируется ограничение или индекс, не имеющий существующего имени (включая один исключительный случай, когда существующее имя может быть дополнительно приукрашено).

Пример соглашения об именовании, которое подходит для основных случаев, выглядит следующим образом:

convention = {
    "ix": "ix_%(column_0_label)s",
    "uq": "uq_%(table_name)s_%(column_0_name)s",
    "ck": "ck_%(table_name)s_%(constraint_name)s",
    "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
    "pk": "pk_%(table_name)s",
}

metadata_obj = MetaData(naming_convention=convention)

Приведенное выше соглашение устанавливает имена для всех ограничений в целевой коллекции MetaData. Например, мы можем наблюдать имя, полученное при создании безымянного ограничения UniqueConstraint:

>>> user_table = Table(
...     "user",
...     metadata_obj,
...     Column("id", Integer, primary_key=True),
...     Column("name", String(30), nullable=False),
...     UniqueConstraint("name"),
... )
>>> list(user_table.constraints)[1].name
'uq_user_name'

Это же свойство действует, даже если мы просто используем флаг Column.unique:

>>> user_table = Table(
...     "user",
...     metadata_obj,
...     Column("id", Integer, primary_key=True),
...     Column("name", String(30), nullable=False, unique=True),
... )
>>> list(user_table.constraints)[1].name
'uq_user_name'

Ключевым преимуществом подхода с использованием соглашения об именовании является то, что имена устанавливаются во время конструирования Python, а не во время эмиссии DDL. При использовании функции Alembic --autogenerate эффект заключается в том, что соглашение об именовании будет явным при создании нового сценария миграции:

def upgrade():
    op.create_unique_constraint("uq_user_name", "user", ["name"])

Приведенная выше строка "uq_user_name" была скопирована из объекта UniqueConstraint, который --autogenerate находится в наших метаданных.

Доступные маркеры включают %(table_name)s, %(referred_table_name)s, %(column_0_name)s, %(column_0_label)s, %(column_0_key)s, %(referred_column_0_name)s и %(constraint_name)s, а также многоколоночные версии каждого из них, включая %(column_0N_name)s, %(column_0_N_name)s, %(referred_column_0_N_name)s, которые отображают все имена колонок, разделенные подчеркиванием или без него. Более подробная информация о каждом из этих соглашений содержится в документации к MetaData.naming_convention.

Соглашение об именовании по умолчанию¶

Значение по умолчанию для MetaData.naming_convention обрабатывает давно известное поведение SQLAlchemy по присвоению имени объекту Index, который создается с помощью параметра Column.index:

>>> from sqlalchemy.sql.schema import DEFAULT_NAMING_CONVENTION
>>> DEFAULT_NAMING_CONVENTION
immutabledict({'ix': 'ix_%(column_0_label)s'})

Усечение длинных имен¶

Если генерируемое имя, особенно те, которые используют маркеры нескольких столбцов, слишком длинное для ограничения длины идентификатора целевой базы данных (например, PostgreSQL имеет ограничение в 63 символа), имя будет детерминистически усечено с помощью 4-символьного суффикса, основанного на md5-хэше длинного имени. Например, приведенное ниже соглашение об именовании будет генерировать очень длинные имена, учитывая используемые имена столбцов:

metadata_obj = MetaData(
    naming_convention={"uq": "uq_%(table_name)s_%(column_0_N_name)s"}
)

long_names = Table(
    "long_names",
    metadata_obj,
    Column("information_channel_code", Integer, key="a"),
    Column("billing_convention_name", Integer, key="b"),
    Column("product_identifier", Integer, key="c"),
    UniqueConstraint("a", "b", "c"),
)

На диалекте PostgreSQL имена длиной более 63 символов будут усекаться, как в следующем примере:

CREATE TABLE long_names (
    information_channel_code INTEGER,
    billing_convention_name INTEGER,
    product_identifier INTEGER,
    CONSTRAINT uq_long_names_information_channel_code_billing_conventi_a79e
    UNIQUE (information_channel_code, billing_convention_name, product_identifier)
)

Приведенный выше суффикс a79e основан на хэше md5 длинного имени и будет генерировать одно и то же значение каждый раз для создания согласованных имен для данной схемы.

Создание пользовательских маркеров для соглашений об именовании¶

Можно также добавить новые маркеры, указав дополнительный маркер и вызываемый объект в словаре naming_convention. Например, если мы хотим назвать наши ограничения внешнего ключа, используя схему GUID, мы можем сделать это следующим образом:

import uuid


def fk_guid(constraint, table):
    str_tokens = (
        [
            table.name,
        ]
        + [element.parent.name for element in constraint.elements]
        + [element.target_fullname for element in constraint.elements]
    )
    guid = uuid.uuid5(uuid.NAMESPACE_OID, "_".join(str_tokens).encode("ascii"))
    return str(guid)


convention = {
    "fk_guid": fk_guid,
    "ix": "ix_%(column_0_label)s",
    "fk": "fk_%(fk_guid)s",
}

Выше, когда мы создадим новый ForeignKeyConstraint, мы получим имя следующим образом:

>>> metadata_obj = MetaData(naming_convention=convention)

>>> user_table = Table(
...     "user",
...     metadata_obj,
...     Column("id", Integer, primary_key=True),
...     Column("version", Integer, primary_key=True),
...     Column("data", String(30)),
... )
>>> address_table = Table(
...     "address",
...     metadata_obj,
...     Column("id", Integer, primary_key=True),
...     Column("user_id", Integer),
...     Column("user_version_id", Integer),
... )
>>> fk = ForeignKeyConstraint(["user_id", "user_version_id"], ["user.id", "user.version"])
>>> address_table.append_constraint(fk)
>>> fk.name
fk_0cd51ab5-8d70-56e8-a83c-86661737766d

Именование ограничений CHECK¶

Объект CheckConstraint настраивается на произвольное SQL-выражение, в котором может присутствовать любое количество столбцов, и дополнительно часто настраивается с помощью необработанной SQL-строки. Поэтому для использования с CheckConstraint обычно используется соглашение, в котором мы ожидаем, что объект уже имеет имя, а затем дополняем его другими элементами соглашения. Типичное соглашение - "ck_%(table_name)s_%(constraint_name)s":

metadata_obj = MetaData(
    naming_convention={"ck": "ck_%(table_name)s_%(constraint_name)s"}
)

Table(
    "foo",
    metadata_obj,
    Column("value", Integer),
    CheckConstraint("value > 5", name="value_gt_5"),
)

Из приведенной выше таблицы получится имя ck_foo_value_gt_5:

CREATE TABLE foo (
    value INTEGER,
    CONSTRAINT ck_foo_value_gt_5 CHECK (value > 5)
)

CheckConstraint также поддерживает маркер %(columns_0_name)s; мы можем использовать его, обеспечив использование элемента Column или column() в выражении ограничения, либо объявив ограничение отдельно от таблицы:

metadata_obj = MetaData(naming_convention={"ck": "ck_%(table_name)s_%(column_0_name)s"})

foo = Table("foo", metadata_obj, Column("value", Integer))

CheckConstraint(foo.c.value > 5)

или с помощью строки column() inline:

from sqlalchemy import column

metadata_obj = MetaData(naming_convention={"ck": "ck_%(table_name)s_%(column_0_name)s"})

foo = Table(
    "foo", metadata_obj, Column("value", Integer), CheckConstraint(column("value") > 5)
)

В обоих случаях будет получено имя ck_foo_value:

CREATE TABLE foo (
    value INTEGER,
    CONSTRAINT ck_foo_value CHECK (value > 5)
)

Определение имени «нулевого столбца» выполняется путем сканирования заданного выражения на наличие объектов столбцов. Если в выражении присутствует более одного столбца, сканирование использует детерминированный поиск, однако структура выражения определяет, какой столбец будет отмечен как «нулевой столбец».

Настройка именования для типов Boolean, Enum и других схем¶

Класс SchemaType относится к объектам типа, таким как Boolean и Enum, которые генерируют ограничение CHECK, сопровождающее тип. Имя для ограничения здесь наиболее непосредственно задается путем передачи параметра «name», например, Boolean.name:

Table("foo", metadata_obj, Column("flag", Boolean(name="ck_foo_flag")))

Функция соглашения об именовании также может быть объединена с этими типами, обычно с помощью соглашения, которое включает %(constraint_name)s и затем применяет имя к типу:

metadata_obj = MetaData(
    naming_convention={"ck": "ck_%(table_name)s_%(constraint_name)s"}
)

Table("foo", metadata_obj, Column("flag", Boolean(name="flag_bool")))

Из приведенной выше таблицы будет получено имя ограничения ck_foo_flag_bool:

CREATE TABLE foo (
    flag BOOL,
    CONSTRAINT ck_foo_flag_bool CHECK (flag IN (0, 1))
)

Классы SchemaType используют специальные внутренние символы, так что соглашение об именовании определяется только во время компиляции DDL. В PostgreSQL существует собственный тип BOOLEAN, поэтому ограничение CHECK для Boolean не нужно; мы можем спокойно установить тип Boolean без имени, несмотря на то, что для ограничений проверки существует соглашение об именовании. Это соглашение будет использоваться для ограничения CHECK только в том случае, если мы работаем с базой данных, не имеющей собственного типа BOOLEAN, например SQLite или MySQL.

Ограничение CHECK также может использовать маркер column_0_name, который хорошо сочетается с SchemaType, поскольку эти ограничения имеют только один столбец:

metadata_obj = MetaData(naming_convention={"ck": "ck_%(table_name)s_%(column_0_name)s"})

Table("foo", metadata_obj, Column("flag", Boolean()))

В приведенной выше схеме будут получены:

CREATE TABLE foo (
    flag BOOL,
    CONSTRAINT ck_foo_flag CHECK (flag IN (0, 1))
)

Функциональные индексы¶

Index поддерживает SQL и функциональные выражения, поддерживаемые целевым бэкендом. Для создания индекса по столбцу, использующему убывающее значение, можно использовать модификатор ColumnElement.desc():

from sqlalchemy import Index

Index("someindex", mytable.c.somecol.desc())

Или с бэкендом, поддерживающим функциональные индексы, таким как PostgreSQL, индекс «без учета регистра» может быть создан с помощью функции lower():

from sqlalchemy import func, Index

Index("someindex", func.lower(mytable.c.somecol))