Исполняемые доки SQLite: Внешние ключи

Q: Как создать внешний ключ в SQLite?

Достаточно дописать REFERENCES othertable(column) к описанию столбца в CREATE TABLE. Например, строка authorid INTEGER REFERENCES authors(id) говорит, что author_id ссылается на запись в таблице authors. Главное условие — столбец, на который вы ссылаетесь, должен быть PRIMARY KEY или иметь ограничение UNIQUE.

Q: Что делает ON DELETE CASCADE в SQLite?

ON DELETE CASCADE означает, что при удалении родительской записи SQLite автоматически удалит все дочерние строки, ссылающиеся на неё. Альтернативы: RESTRICT — запретить удаление, SET NULL — обнулить FK-столбец, SET DEFAULT — поставить значение по умолчанию, и NO ACTION (поведение по умолчанию, на практике то же, что RESTRICT). Выбор зависит от того, имеют ли смысл дочерние записи без родителя.

Внешний ключ — это указатель между таблицами

Внешний ключ (foreign key) в SQLite — это столбец одной таблицы, значение которого обязано совпадать с какой-то строкой в другой таблице. Именно так реляционная база говорит: «эта строка из posts принадлежит вот тому автору из authors», и при этом не приходится дублировать имя и email автора в каждой записи поста.

Разберём на самом простом примере: родительская и дочерняя таблицы, связанные через внешний ключ.

PRAGMA foreign_keys = ON;

CREATE TABLE authors (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT NOT NULL,
    author_id INTEGER REFERENCES authors(id)
);

INSERT INTO authors (name) VALUES ('Ада'), ('Борис');
INSERT INTO posts (title, author_id) VALUES ('Привет', 1), ('Заметки', 2);

SELECT p.title, a.name
FROM posts p JOIN authors a ON a.id = p.author_id;

author_id INTEGER REFERENCES authors(id) — это и есть полное объявление внешнего ключа. Смысл такой: в этой колонке лежит id из таблицы authors. Теперь база знает, что две таблицы связаны, и — если проверка включена — откажется вставлять строки, которые ссылаются на несуществующих авторов.

Внешние ключи в SQLite по умолчанию выключены

Это, пожалуй, самый важный факт про foreign key в SQLite, и он каждый раз застаёт врасплох: SQLite разбирает конструкцию REFERENCES, но не следит за её соблюдением, пока вы сами об этом не попросите. Причина историческая — совместимость со старым кодом, который писался ещё до появления этой возможности.

Посмотрим, что происходит без включённой проверки:

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT,
    author_id INTEGER REFERENCES authors(id)
);

-- автор 999 не существует, но SQLite спокойно принимает это:
INSERT INTO posts (title, author_id) VALUES ('Сирота', 999);

SELECT * FROM posts;

Сиротская запись преспокойно записалась. Чтобы защита реально работала, в начале каждого подключения выполняйте PRAGMA foreign_keys = ON;:

PRAGMA foreign_keys = ON;

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT,
    author_id INTEGER REFERENCES authors(id)
);

INSERT INTO posts (title, author_id) VALUES ('Сирота', 999);

Теперь вставка падает с ошибкой FOREIGN KEY constraint failed. Прагма действует на уровне соединения, а не базы — настройка не сохраняется в файле. Каждое приложение, каждая сессия в CLI, каждая тестовая фикстура обязаны выставлять её заново. В продакшене обычно сразу после открытия соединения выполняют PRAGMA foreign_keys = ON;.

Что на самом деле требует клауза REFERENCES

Колонка, на которую вы ссылаетесь, должна быть PRIMARY KEY либо иметь ограничение UNIQUE. Только так SQLite может гарантировать однозначность поиска. Типы тоже желательно делать совместимыми — SQLite относится к типам вольно, но их смешение почти всегда приводит к сюрпризам.

Внешний ключ в SQLite можно описать двумя способами. Прямо в строке с колонкой:

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);

CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    author_id INTEGER REFERENCES authors(id),
    title     TEXT
);

SELECT sql FROM sqlite_master WHERE name = 'posts';

Либо в виде отдельного ограничения на уровне таблицы — этот вариант обязателен, когда внешний ключ состоит из нескольких столбцов:

CREATE TABLE posts (id INTEGER PRIMARY KEY, title TEXT);
CREATE TABLE tags  (id INTEGER PRIMARY KEY, name TEXT);

CREATE TABLE post_tags (
    post_id INTEGER NOT NULL,
    tag_id  INTEGER NOT NULL,
    PRIMARY KEY (post_id, tag_id),
    FOREIGN KEY (post_id) REFERENCES posts(id),
    FOREIGN KEY (tag_id)  REFERENCES tags(id)
);

SELECT sql FROM sqlite_master WHERE name = 'post_tags';

Обе формы создают абсолютно одинаковые ограничения. Выбирайте ту, которая лучше читается для конкретной таблицы.

ON DELETE: что будет с дочерними записями

Когда вы удаляете родительскую строку, SQLite должен как-то поступить с дочерними записями, которые на неё ссылаются. Поведение задаётся через ON DELETE:

PRAGMA foreign_keys = ON;

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);

CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT,
    author_id INTEGER REFERENCES authors(id) ON DELETE CASCADE
);

INSERT INTO authors (id, name) VALUES (1, 'Ada');
INSERT INTO posts (title, author_id) VALUES ('A'), ('B') ON CONFLICT DO NOTHING;
INSERT INTO posts (title, author_id) VALUES ('A', 1), ('B', 1);

DELETE FROM authors WHERE id = 1;
SELECT * FROM posts;

Удаление Ады заодно снесло оба её поста. Вот какие варианты у нас есть:

CASCADE — удалять и потомков. Подходит для «принадлежащих» данных: посты автора, позиции заказа и тому подобное.
SET NULL — занулить колонку с внешним ключом. Удобно, когда дочерние записи должны жить дальше без родителя (скажем, комментарии удалённого пользователя становятся анонимными).
SET DEFAULT — выставить в колонке FK значение по умолчанию, объявленное в схеме.
RESTRICT — запретить удаление, если есть хоть один потомок. Падает сразу же, на самом операторе.
NO ACTION — поведение по умолчанию. На практике почти то же самое, что RESTRICT (проверка просто откладывается до commit'а, но итог тот же — висячих потомков оставить нельзя).

ON UPDATE работает по той же логике, но уже для изменений ключа родителя — правда, первичные ключи правят редко.

Ошибка foreign key constraint failed: что она значит

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

sqlite> INSERT INTO posts (title, author_id) VALUES ('Stray', 999);
Runtime error: FOREIGN KEY constraint failed

Либо автора с id 999 не существует, либо вы перепутали типы колонок. Сначала вставьте родительскую запись или исправьте значение.

Во-вторых, удаление (или обновление) родителя, у которого ещё есть дочерние записи, если внешний ключ объявлен с RESTRICT или NO ACTION:

sqlite> DELETE FROM authors WHERE id = 1;
Runtime error: FOREIGN KEY constraint failed

Тогда либо сначала удаляйте дочерние записи, либо переключите внешний ключ на ON DELETE CASCADE/SET NULL, если каскад — это как раз то, что вам нужно.

Есть ещё менее известный родственник этой ошибки — FOREIGN KEY mismatch. Она вылетает, когда колонка, на которую ссылается внешний ключ, не является первичным ключом или не помечена как UNIQUE, либо когда не совпадает количество колонок. Это уже ошибка схемы, а не данных.

Как добавить внешний ключ в существующую таблицу SQLite

Возможности ALTER TABLE в SQLite довольно скромные: добавить новую колонку с внешним ключом можно, а вот навесить внешний ключ на уже существующую колонку — нет. Стандартный обходной путь — классический танец с переименованием и пересозданием таблицы:

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT NOT NULL,
    author_id INTEGER
);

INSERT INTO authors (id, name) VALUES (1, 'Ada');
INSERT INTO posts (title, author_id) VALUES ('Привет', 1), ('Мир', 1);

PRAGMA foreign_keys = OFF;
BEGIN;

CREATE TABLE posts_new (
    id        INTEGER PRIMARY KEY,
    title     TEXT NOT NULL,
    author_id INTEGER REFERENCES authors(id) ON DELETE CASCADE
);

INSERT INTO posts_new SELECT id, title, author_id FROM posts;

DROP TABLE posts;
ALTER TABLE posts_new RENAME TO posts;

COMMIT;
PRAGMA foreign_keys = ON;

SELECT sql FROM sqlite_master WHERE name = 'posts';

Схема такая: выключаем проверку, создаём новую таблицу с нужными ограничениями, переливаем данные, дропаем старую, переименовываем. BEGIN/COMMIT делают всё это атомарным. В конце включаем проверку обратно — SQLite пройдётся по всем существующим строкам и сверит их с новыми ограничениями. Но учтите: если данные окажутся битыми, транзакция к этому моменту уже закоммичена, так что лучше проверить заранее, если переживаете за целостность.

После миграции запустите PRAGMA foreign_key_check; — он покажет, не осталось ли «осиротевших» строк.

Реалистичный пример схемы

Соберём всё вместе на маленькой схеме блога: родительские таблицы, дочерние и связующая таблица для тегов в формате many-to-many:

PRAGMA foreign_keys = ON;

CREATE TABLE authors (
    id    INTEGER PRIMARY KEY,
    email TEXT UNIQUE NOT NULL
);

CREATE TABLE posts (
    id         INTEGER PRIMARY KEY,
    author_id  INTEGER NOT NULL REFERENCES authors(id) ON DELETE CASCADE,
    title      TEXT NOT NULL,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE tags (
    id   INTEGER PRIMARY KEY,
    name TEXT UNIQUE NOT NULL
);

CREATE TABLE post_tags (
    post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    tag_id  INTEGER NOT NULL REFERENCES tags(id)  ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

Здесь стоит обратить внимание на три момента. Колонка author_id помечена как NOT NULL — у каждого поста обязательно должен быть автор. Внешний ключ posts → authors идёт с каскадом, поэтому при удалении автора уйдут и все его посты. Связующая таблица post_tags каскадирует с обеих сторон: удалили пост или тег — связи подчищаются сами.

Привычки, которые избавят от боли в будущем

Включайте PRAGMA foreign_keys = ON; на каждом подключении. Сделайте это частью процедуры открытия базы в приложении, а не тем, что нужно «не забыть».
Заведите индекс на колонке внешнего ключа. SQLite индексирует ключ родительской таблицы автоматически, а вот дочернюю — нет; при этом ON DELETE CASCADE каждый раз ищет связанные строки в дочерней таблице, когда удаляется родитель.
Осознанно выбирайте поведение ON DELETE. Значение по умолчанию (NO ACTION) безопасно, но при любой попытке навести порядок вы будете натыкаться на foreign key constraint failed. Решите, что должно произойти, и пропишите это явно.
После миграций или массовых импортов запускайте PRAGMA foreign_key_check; — это поможет отловить «осиротевшие» строки до того, как они превратятся в баги.

Дальше: INNER JOIN

Внешние ключи описывают связь, а вот доставать данные через эту связь нужно с помощью join'ов. На следующей странице разберём INNER JOIN — как объединять строки из связанных таблиц и забирать из каждой именно те колонки, что вам нужны.

Часто задаваемые вопросы

Как создать внешний ключ в SQLite?

Достаточно дописать REFERENCES other_table(column) к описанию столбца в CREATE TABLE. Например, строка author_id INTEGER REFERENCES authors(id) говорит, что author_id ссылается на запись в таблице authors. Главное условие — столбец, на который вы ссылаетесь, должен быть PRIMARY KEY или иметь ограничение UNIQUE.

Почему внешние ключи в SQLite не срабатывают?

SQLite разбирает объявления внешних ключей, но по умолчанию их не проверяет — пока вы явно не включите эту проверку. В начале каждого соединения нужно выполнять PRAGMA foreign_keys = ON;. Настройка действует только в рамках текущего подключения и не сохраняется в файле БД, поэтому и драйверы, и CLI должны включать её при каждом коннекте.

Что делает ON DELETE CASCADE в SQLite?

ON DELETE CASCADE означает, что при удалении родительской записи SQLite автоматически удалит все дочерние строки, ссылающиеся на неё. Альтернативы: RESTRICT — запретить удаление, SET NULL — обнулить FK-столбец, SET DEFAULT — поставить значение по умолчанию, и NO ACTION (поведение по умолчанию, на практике то же, что RESTRICT). Выбор зависит от того, имеют ли смысл дочерние записи без родителя.

Как исправить ошибку foreign key constraint failed в SQLite?

Эта ошибка возникает в двух случаях: вы вставляете или обновляете строку со значением FK, которому нет соответствия в родительской таблице, либо пытаетесь удалить родителя, у которого ещё есть дочерние записи. Решение — сначала убедитесь, что нужная запись в родительской таблице действительно существует, либо настройте ON DELETE CASCADE, чтобы зависимые строки удалялись автоматически.

Связанные концепции

Внешние ключи в SQLite: REFERENCES и ON DELETE