← Cloudflare D1 / d1 / sql-api

Определение внешних ключей

D1 поддерживает определение и применение ограничений внешних ключей во всех таблицах базы данных.

Ограничения внешнего ключа позволяют обеспечивать связи между таблицами. Например, с помощью внешних ключей можно создать жёсткую привязку между user_id в users таблицу и user_id в orders таблицы, чтобы нельзя было создать заказ для несуществующего пользователя.

Ограничения внешнего ключа также могут запрещать удаление строк, на которые ссылаются строки в других таблицах. Например, удаление строк из users таблице, когда строки в orders таблицы ссылаются на них.

По умолчанию D1 обеспечивает соблюдение ограничений внешнего ключа во всех запросах и миграциях. Это соответствует поведению, которое вы увидите при установке PRAGMA foreign_keys = on в SQLite для каждой транзакции.

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

Применение ограничений внешних ключей в D1 аналогично применению в SQLite PRAGMA foreign_keys = on директиву. Поскольку D1 выполняет каждый запрос внутри неявной транзакции, пользовательские запросы не могут изменить это во время выполнения запроса или миграции.

Вместо этого D1 позволяет вызывать PRAGMA defer_foreign_keys = on или off, что позволяет временно нарушать ограничения внешнего ключа (до завершения текущей транзакции).

Вызов PRAGMA defer_foreign_keys = off не отключает проверку внешних ключей за пределами текущей транзакции. Если к концу транзакции у вас останутся неразрешённые нарушения внешних ключей, она завершится ошибкой FOREIGN KEY constraint failed ошибка.

Чтобы отложить проверку внешних ключей, установите PRAGMA defer_foreign_keys = on в начале транзакции или перед изменениями, которые нарушили бы ограничения:

-- Defer foreign key enforcement in this transaction.
PRAGMA defer_foreign_keys = on

-- Run your CREATE TABLE or ALTER TABLE / COLUMN statements
ALTER TABLE users ...

-- This is implicit if not set by the end of the transaction.
PRAGMA defer_foreign_keys = off

Вы также можете явно задать PRAGMA defer_foreign_keys = off сразу после того, как вы устраните оставшиеся ограничения внешнего ключа. Если незакрытые ограничения внешнего ключа всё ещё остаются, вы получите FOREIGN KEY constraint failed ошибку, и вам нужно будет устранить нарушение.

Связь по внешнему ключу можно определить при создании таблицы с помощью CREATE TABLE либо при добавлении столбца в существующую таблицу через ALTER TABLE оператор.

В качестве примера рассмотрим интернет-магазин с двумя таблицами:

• A users таблицу, которая определяет общие свойства учётной записи пользователя, включая уникальный user_id идентификатор.
• Одна orders таблицу, которая связывает заказ с user_id в таблице user.

Это сопоставление определяется как FOREIGN KEY, что гарантирует следующее:

• Нельзя удалить строку из users таблицу, что нарушило бы ограничение внешнего ключа. Это означает, что у вас не может быть заказов, не связанных с существующим пользователем.
• orders всегда определяются относительно допустимого user_id, снижая риск создания заказов, ссылающихся на недействительных (или несуществующих) пользователей.

CREATE TABLE users (
    user_id INTEGER PRIMARY KEY,
    email_address TEXT,
    name TEXT,
    metadata TEXT
)

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    status INTEGER,
    item_desc TEXT,
    shipped_date INTEGER,
    user_who_ordered INTEGER,
    FOREIGN KEY(user_who_ordered) REFERENCES users(user_id)
)

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

Можно определить действия как часть определений внешних ключей, чтобы ограничить или распространить изменения родительской строки (REFERENCES table(column)). Определение действия упрощает понимание того, как в приложении применяются ограничения внешнего ключа, и помогает либо очищать связанные данные, либо предотвращать их изоляцию от остальных записей.

Существует пять действий, которые можно задать при определении ON UPDATE и/или ON DELETE предложения как часть связи внешнего ключа. Также можно определить разные действия для ON UPDATE и ON DELETE в зависимости от ваших требований.

• CASCADE - обновление или удаление родительского ключа удаляет все связанные с ним дочерние ключи (строки).
• RESTRICT - родительский ключ нельзя обновить или удалить, если any дочерний ключ ссылается на него. В отличие от применения внешних ключей по умолчанию, для связей с RESTRICT при применении сразу возвращают ошибки, а не в конце транзакции.
• SET DEFAULT - устанавливает дочерний столбец (или столбцы), на которые ссылается определение внешнего ключа, в DEFAULT значение, определённое в схеме. Если не DEFAULT задаётся для дочерних столбцов, это действие использовать нельзя.
• SET NULL - устанавливает дочерний столбец (или столбцы), на которые ссылается определение внешнего ключа, в SQL NULL.
• NO ACTION - не выполняет никаких действий.

В следующем примере при удалении пользователя из users таблице удалит все связанные строки в scores таблицу, так как вы определили ON DELETE CASCADE. Удалите все связанные строки в scores таблицу, если вы не хотите сохранять результаты для полностью удалённых пользователей. Это может означать, что другое пользователи больше не могут находить оценки, которые всё ещё были действительны, или ссылаться на них.

CREATE TABLE users (
    user_id INTEGER PRIMARY KEY,
    email_address TEXT,
)

CREATE TABLE scores (
    score_id INTEGER PRIMARY KEY,
    game TEXT,
    score INTEGER,
    player_id INTEGER,
    FOREIGN KEY(player_id) REFERENCES users(user_id) ON DELETE CASCADE
)

• Прочтите SQLite FOREIGN KEY ↗ документация.
• Узнайте, как используйте D1 Workers Binding API изнутри Worker.
• Узнайте, как работают миграции базы данных с D1.