Поддержка внешних ключей в SQLite: ограничения и настройка

Обзор

В этом документе описывается поддержка ограничений внешних ключей SQL, введённая в SQLite версии 3.6.19 (2009-10-14).

Первый раздел вводит понятие внешнего ключа SQL на примере и определяет терминологию, используемую в остальной части документа. Во втором разделе описываются действия, необходимые для включения ограничений внешних ключей в SQLite, поскольку они по умолчанию отключены. Третий раздел охватывает необходимые индексы для корректной работы ограничений, а также рекомендуемые для повышения их эффективности.

Четвертый раздел раскрывает расширенные возможности SQLite, связанные с внешними ключами, в пятом показываются адаптации команд ALTER TABLE и DROP TABLE для поддержки этих ограничений. Наконец, шестой раздел перечисляет отсутствующие возможности и ограничения текущей реализации.

Этот документ не предлагает полного описания синтаксиса для создания ограничений внешних ключей в SQLite. Полную информацию можно найти в документации по оператору CREATE TABLE.

По этой теме полезно отдельно посмотреть EXPLAIN QUERY PLAN: план выполнения SQL-запроса в SQLite, чтобы расширить контекст и сравнить подходы.

По этой теме полезно отдельно посмотреть Создание Flutter-приложения с SQLite, BLoC и Streams, чтобы расширить контекст и сравнить подходы.

1. Что такое ограничения внешних ключей в SQLite

Ограничения внешних ключей SQL используются для обеспечения отношений «существования» между таблицами. Рассмотрим схему базы данных, созданную с помощью следующих SQL-команд:

CREATE TABLE artist( artistid INTEGER PRIMARY KEY, artistname TEXT
);
CREATE TABLE track( trackid INTEGER, trackname TEXT, trackartist INTEGER -- Должен ссылаться на artist.artistid!
);

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

Из-за этого могут появляться «осиротевшие» записи (записи без родительской строки), что приведет к сбоям в работе приложения и усложнит код.

Если требуется установить связь между таблицами artist и track, необходимо добавить ограничение внешнего ключа SQL в схему базы данных. Это можно сделать, изменив объявление таблицы track, как показано ниже:

CREATE TABLE track( trackid INTEGER, trackname TEXT, trackartist INTEGER, FOREIGN KEY(trackartist) REFERENCES artist(artistid)
);

Для обеспечения связи между artist и track, необходимо включить ограничение внешнего ключа SQL в схему базы данных. Объявление таблицы track должно быть следующим:

trackartist IS NULL OR EXISTS(SELECT 1 FROM artist WHERE artistid=trackartist)

Совет: если приложение требует более строгого отношения между artist и track, при котором значения NULL не допускаются в столбце trackartist, просто добавьте соответствующее ограничение NOT NULL в схему.

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

Следующий сеанс командной строки SQLite иллюстрирует эффект ограничения внешнего ключа, добавленного в таблицу track:

sqlite> SELECT * FROM artist;
artistid artistname
-------- -----------------
1 Dean Martin
2 Frank Sinatra

sqlite> SELECT * FROM track;
trackid trackname trackartist
------- --------------- -----------
11 That's Amore 1
12 Christmas Blues 1
13 My Way 2

sqlite> -- Это завершается неудачей, так как значение в столбце trackartist (3) sqlite> -- не имеет соответствия в таблице artist. sqlite> INSERT INTO track VALUES(14, 'Mr. Bojangles', 3); SQL error: foreign key constraint failed.

sqlite> -- Это успешно, потому что в trackartist вставляется NULL. В этом случае
sqlite> -- соответствующая строка в таблице artist не требуется.
sqlite> INSERT INTO track VALUES(14, 'Mr. Bojangles', NULL);

sqlite> -- Попытка изменить поле trackartist записи после её вставки
sqlite> -- также не работает, поскольку новое значение trackartist (3)
sqlite> -- по-прежнему не соответствует ни одной строке в таблице artist.
sqlite> UPDATE track SET trackartist = 3 WHERE trackname = 'Mr. Bojangles';
SQL error: foreign key constraint failed

sqlite> -- Вставляем необходимую строку в таблицу artist. После этого можно
sqlite> -- обновить вставленную строку, установив trackartist равным 3.
sqlite> INSERT INTO artist VALUES(3, 'Sammy Davis Jr.');
sqlite> UPDATE track SET trackartist = 3 WHERE trackname = 'Mr. Bojangles';

sqlite> -- Теперь можно вставлять новые треки с этим исполнителем без нарушения
sqlite> -- ограничения внешнего ключа:
sqlite> INSERT INTO track VALUES(15, 'Boogie Woogie', 3);

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

sqlite> -- Попытка удалить запись исполнителя "Frank Sinatra" завершается неудачей, поскольку
sqlite> -- таблица track содержит строку, ссылающуюся на неё.
sqlite> DELETE FROM artist WHERE artistname = 'Frank Sinatra';
SQL error: foreign key constraint failed

sqlite> -- Удаляем все записи из таблицы track, ссылающиеся на исполнителя
sqlite> -- "Frank Sinatra". Только после этого можно удалить исполнителя.
sqlite> DELETE FROM track WHERE trackname = 'My Way';
sqlite> DELETE FROM artist WHERE artistname = 'Frank Sinatra';

sqlite> -- Пытаемся обновить artistid строки в таблице artist, пока
sqlite> -- в таблице track существуют записи, ссылающиеся на неё.
sqlite> UPDATE artist SET artistid=4 WHERE artistname = 'Dean Martin';
SQL error: foreign key constraint failed

sqlite> -- После удаления всех записей, ссылающихся на строку в таблице artist,
sqlite> -- можно изменить artistid этой строки.
sqlite> DELETE FROM track WHERE trackname IN('That''s Amore', 'Christmas Blues');
sqlite> UPDATE artist SET artistid=4 WHERE artistname = 'Dean Martin';

SQLite использует следующую терминологию:

Родительская таблица — это таблица, на которую ссылается ограничение внешнего ключа. Родительской таблицей в примере данного раздела является таблица artist. В некоторых книгах и статьях она называется таблица-источник, что, пожалуй, более точно, но нередко приводит к путанице.

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

Родительский ключ (parent key) — это столбец или набор столбцов в родительской таблице, на которые ссылается ограничение внешнего ключа. Как правило, но не всегда, это первичный ключ родительской таблицы. Родительский ключ должен быть именованным столбцом или столбцами в родительской таблице, а не rowid.

Дочерний ключ (child key) — это столбец или набор столбцов в дочерней таблице, на которые распространяется ограничение внешнего ключа и которые содержат предложение REFERENCES.

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

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

• При сравнении текстовых значений всегда используется последовательность сортировки (collating sequence), связанная со столбцом родительского ключа.
• При сравнении значений, если столбец родительского ключа имеет аффинность (affinity), эта аффинность применяется к значению дочернего ключа перед выполнением сравнения.

2. Как включить внешние ключи в SQLite (PRAGMA foreign_keys)

Для использования ограничений внешних ключей в SQLite библиотека должна быть скомпилирована без определения SQLITE_OMIT_FOREIGN_KEY и SQLITE_OMIT_TRIGGER. Если SQLITE_OMIT_TRIGGER определён, но SQLITE_OMIT_FOREIGN_KEY — нет, то SQLite ведёт себя так же, как до версии 3.6.19 (2009-10-14): определения внешних ключей разбираются и могут быть запрошены с помощью PRAGMA foreign_key_list, однако ограничения внешних ключей не применяются.

Команда PRAGMA foreign_keys в этой конфигурации не выполняет никаких действий. Если определён OMIT_FOREIGN_KEY, то определения внешних ключей не могут быть даже разобраны — попытка указать определение внешнего ключа является синтаксической ошибкой.

Предполагая, что библиотека скомпилирована с включёнными ограничениями внешних ключей, их всё равно необходимо включить в приложении во время выполнения с помощью команды PRAGMA foreign_keys. Например:

sqlite> PRAGMA foreign_keys = ON;

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

Внимательные разработчики не будут делать никаких предположений о том, включены ли внешние ключи по умолчанию, а вместо этого будут включать или отключать их по мере необходимости. Приложение также может использовать оператор PRAGMA foreign_keys для определения того, включены ли в данный момент внешние ключи. Следующий сеанс командной строки демонстрирует это:

sqlite> PRAGMA foreign_keys;
0
sqlite> PRAGMA foreign_keys = ON;
sqlite> PRAGMA foreign_keys;
1
sqlite> PRAGMA foreign_keys = OFF;
sqlite> PRAGMA foreign_keys;
0

Совет: если команда PRAGMA foreign_keys не возвращает никаких данных вместо одной строки, содержащей 0 или 1, то используемая версия SQLite не поддерживает внешние ключи — либо потому, что она старше 3.6.19, либо потому, что была скомпилирована с определёнными SQLITE_OMIT_FOREIGN_KEY или SQLITE_OMIT_TRIGGER.

Невозможно включить или отключить ограничения внешних ключей в середине многооператорной транзакции (когда SQLite не находится в режиме автофиксации). Попытка сделать это не возвращает ошибку — она просто не имеет никакого эффекта.

3. Индексы для корректной работы ограничений внешних ключей

Как правило, родительский ключ ограничения внешнего ключа является первичным ключом родительской таблицы. Если это не первичный ключ, то столбцы родительского ключа должны в совокупности подпадать под ограничение UNIQUE или иметь индекс UNIQUE. Если столбцы родительского ключа имеют индекс UNIQUE, то этот индекс должен использовать последовательности сравнения, указанные в операторе CREATE TABLE для родительской таблицы. Например:

CREATE TABLE parent(a PRIMARY KEY, b UNIQUE, c, d, e, f);
CREATE UNIQUE INDEX i1 ON parent(c, d);
CREATE INDEX i2 ON parent(e);
CREATE UNIQUE INDEX i3 ON parent(f COLLATE nocase);

CREATE TABLE child1(f, g REFERENCES parent(a)); -- Ok
CREATE TABLE child2(h, i REFERENCES parent(b)); -- Ok
CREATE TABLE child3(j, k, FOREIGN KEY(j, k) REFERENCES parent(c, d)); -- Ok
CREATE TABLE child4(l, m REFERENCES parent(e)); -- Error!
CREATE TABLE child5(n, o REFERENCES parent(f)); -- Error!
CREATE TABLE child6(p, q, FOREIGN KEY(p, q) REFERENCES parent(b, c)); -- Error!
CREATE TABLE child7(r REFERENCES parent(c)); -- Error!

Ограничения внешних ключей, созданные как часть таблиц child1, child2 и child3, полностью корректны. Внешний ключ, объявленный как часть таблицы child4, является ошибкой, потому что, несмотря на то что столбец родительского ключа индексирован, индекс не является UNIQUE.

Внешний ключ для таблицы child5 является ошибкой, потому что, несмотря на то что столбец родительского ключа имеет уникальный индекс, этот индекс использует другую последовательность сравнения. Таблицы child6 и child7 некорректны, потому что, хотя обе имеют индексы UNIQUE на своих родительских ключах, ключи не являются точным соответствием столбцам одного индекса UNIQUE.

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

Ошибки, сообщаемые при изменении содержимого, называются «ошибками DML», а ошибки, сообщаемые при изменении схемы, — «ошибками DDL». Иными словами, неправильно настроенные ограничения внешних ключей, требующие изучения как дочерней, так и родительской таблицы, являются ошибками DML.

Сообщение об ошибке DML внешнего ключа на английском языке обычно звучит как foreign key mismatch, но также может быть no such table, если родительская таблица не существует. Ошибки DML внешнего ключа сообщаются в следующих случаях:

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

Последний пункт иллюстрируется следующим примером:

CREATE TABLE parent2(a, b, PRIMARY KEY(a,b));
CREATE TABLE child8(x, y, FOREIGN KEY(x,y) REFERENCES parent2); -- Ok
CREATE TABLE child9(x REFERENCES parent2); -- Error!
CREATE TABLE child10(x,y,z, FOREIGN KEY(x,y,z) REFERENCES parent2); -- Error!

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

Индексы не требуются для столбцов дочернего ключа, но они почти всегда полезны. Возвращаясь к примеру из раздела 1, каждый раз, когда приложение удаляет строку из таблицы artist (родительской таблицы), оно выполняет эквивалент следующего оператора SELECT для поиска ссылающихся строк в таблице track (дочерней таблице):

SELECT rowid FROM track WHERE trackartist = ?

где ? заменяется значением столбца artistid записи, удаляемой из таблицы artist (столбец trackartist является дочерним ключом, а столбец artistid — родительским ключом). Или, в более общем виде:

SELECT rowid FROM <child-table> WHERE <child-key> = :parent_key_value

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

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

Таким образом, в большинстве реальных систем индекс должен быть создан на столбцах дочернего ключа каждого ограничения внешнего ключа. Индекс дочернего ключа не обязан быть (и обычно не будет) индексом UNIQUE. Возвращаясь к примеру из раздела 1, полная схема базы данных для эффективной реализации ограничения внешнего ключа может выглядеть следующим образом:

CREATE TABLE artist( artistid INTEGER PRIMARY KEY, artistname TEXT
);
CREATE TABLE track( trackid INTEGER, trackname TEXT, trackartist INTEGER REFERENCES artist
);
CREATE INDEX trackindex ON track(trackartist);

Приведённый выше блок использует сокращённую форму для создания ограничения внешнего ключа. Присоединение предложения REFERENCES <parent-table> к определению столбца создаёт ограничение внешнего ключа, которое сопоставляет столбец с первичным ключом <parent-table>. Обратитесь к документации по CREATE TABLE для получения дополнительных сведений.

4. Расширенные возможности ограничений внешнего ключа

4.1. Составные ограничения внешнего ключа

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

CREATE TABLE album( albumartist TEXT, albumname TEXT, albumcover BINARY, PRIMARY KEY(albumartist, albumname)
);
CREATE TABLE song( songid INTEGER, songartist TEXT, songalbum TEXT, songname TEXT, FOREIGN KEY(songartist, songalbum) REFERENCES album(albumartist, albumname)
);

В этой системе каждая запись в таблице song обязана соответствовать записи в таблице album с той же комбинацией исполнителя и альбома.

Родительский и дочерний ключи должны иметь одинаковую кардинальность. В SQLite, если любой из столбцов дочернего ключа (в данном случае songartist и songalbum) имеет значение NULL, то соответствующая строка в родительской таблице не требуется.

4.2. Отложенные ограничения внешнего ключа

Каждое ограничение внешнего ключа в SQLite классифицируется как немедленное (immediate) или отложенное (deferred). По умолчанию ограничения внешнего ключа являются немедленными. Все примеры внешних ключей, представленные до сих пор, относились к немедленным ограничениям.

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

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

Если текущий оператор не находится внутри явной транзакции (блока BEGIN / COMMIT / ROLLBACK), то неявная транзакция фиксируется сразу после завершения выполнения оператора. В этом случае отложенные ограничения ведут себя так же, как немедленные.

Чтобы пометить ограничение внешнего ключа как отложенное, его объявление должно включать следующее предложение:

DEFERRABLE INITIALLY DEFERRED -- Отложенное ограничение внешнего ключа

Полный синтаксис для указания ограничений внешнего ключа доступен в документации по CREATE TABLE. Замена приведённой выше фразы любой из следующих создаёт немедленное ограничение внешнего ключа:

NOT DEFERRABLE INITIALLY DEFERRED -- Немедленное ограничение внешнего ключа
NOT DEFERRABLE INITIALLY IMMEDIATE -- Немедленное ограничение внешнего ключа
NOT DEFERRABLE -- Немедленное ограничение внешнего ключа
DEFERRABLE INITIALLY IMMEDIATE -- Немедленное ограничение внешнего ключа
DEFERRABLE -- Немедленное ограничение внешнего ключа

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

Следующий пример иллюстрирует эффект использования отложенного ограничения внешнего ключа:

-- Схема базы данных. Обе таблицы изначально пусты.
CREATE TABLE artist( artistid INTEGER PRIMARY KEY, artistname TEXT
);
CREATE TABLE track( trackid INTEGER, trackname TEXT, trackartist INTEGER REFERENCES artist(artistid) DEFERRABLE INITIALLY DEFERRED
);

sqlite3> -- Если бы ограничение внешнего ключа было немедленным, этот INSERT
sqlite3> -- вызвал бы ошибку (поскольку в таблице artist нет строки с
sqlite3> -- artistid=5). Но так как ограничение отложено и есть открытая
sqlite3> -- транзакция, ошибки не возникает.
sqlite3> BEGIN;
sqlite3> INSERT INTO track VALUES(1, 'White Christmas', 5);

sqlite3> -- Следующий COMMIT завершается ошибкой, так как база данных находится
sqlite3> -- в состоянии, не удовлетворяющем отложенному ограничению внешнего ключа.
sqlite3> -- Транзакция остаётся открытой.
sqlite3> COMMIT;
SQL error: foreign key constraint failed

sqlite3> -- После вставки строки в таблицу artist с artistid=5
sqlite3> -- отложенное ограничение внешнего ключа удовлетворено. После этого
sqlite3> -- можно зафиксировать транзакцию без ошибок.
sqlite3> INSERT INTO artist VALUES(5, 'Bing Crosby');
sqlite3> COMMIT;

Вложенная транзакция savepoint (точка сохранения) может быть освобождена (RELEASE) в то время, когда база данных находится в состоянии, не удовлетворяющем отложенному ограничению внешнего ключа. Транзакционная точка сохранения (невложенная точка сохранения, открытая в момент, когда не было активной транзакции), напротив, подчиняется тем же ограничениям, что и COMMIT — попытка выполнить RELEASE в таком состоянии завершится ошибкой.

Если оператор COMMIT (или RELEASE транзакционной точки SAVEPOINT) завершается ошибкой из-за того, что база данных в данный момент находится в состоянии, нарушающем отложенное ограничение внешнего ключа, и при этом существуют вложенные точки сохранения, вложенные точки сохранения остаются открытыми.

4.3. Действия ON DELETE и ON UPDATE

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

Действие ON DELETE и ON UPDATE, связанное с каждым внешним ключом в базе данных SQLite, является одним из следующих: NO ACTION, RESTRICT, SET NULL, SET DEFAULT или CASCADE. Если действие явно не указано, по умолчанию используется NO ACTION.

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

RESTRICT: действие RESTRICT означает, что приложению запрещено удалять (для ON DELETE RESTRICT) или изменять (для ON UPDATE RESTRICT) родительский ключ, если существует один или несколько дочерних ключей, связанных с ним.

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

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

SET NULL: если настроено действие SET NULL, то при удалении родительского ключа (для ON DELETE SET NULL) или его изменении (для ON UPDATE SET NULL) столбцы дочернего ключа всех строк в дочерней таблице, связанных с этим родительским ключом, устанавливаются в значение SQL NULL.

SET DEFAULT: действия SET DEFAULT аналогичны SET NULL, за исключением того, что каждый из столбцов дочернего ключа устанавливается в значение по умолчанию для этого столбца вместо NULL. Подробнее о том, как значения по умолчанию назначаются столбцам таблицы, см. в документации по CREATE TABLE.

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

Например, добавление предложения ON UPDATE CASCADE к внешнему ключу, как показано ниже, расширяет схему из раздела 1, позволяя пользователю обновлять столбец artistid (родительский ключ ограничения внешнего ключа) без нарушения ссылочной целостности:

-- Database schema
CREATE TABLE artist( artistid INTEGER PRIMARY KEY, artistname TEXT
);
CREATE TABLE track( trackid INTEGER, trackname TEXT, trackartist INTEGER REFERENCES artist(artistid) ON UPDATE CASCADE
);

sqlite> -- Update the artistid column of the artist record for "Dean Martin".
sqlite> -- Normally, this would raise a constraint, as it would orphan the two
sqlite> -- dependent records in the track table. However, the ON UPDATE CASCADE clause
sqlite> -- attached to the foreign key definition causes the update to "cascade"
sqlite> -- to the дочерняя таблица, preventing the foreign key constraint violation.
sqlite> UPDATE artist SET artistid = 100 WHERE artistname = 'Dean Martin';

sqlite> SELECT * FROM artist;
artistid artistname
-------- -----------------
2 Frank Sinatra
100 Dean Martin

sqlite> SELECT * FROM track;
trackid trackname trackartist
------- --------------- -----------
11 That's Amore 100
12 Christmas Blues 100
13 My Way 2

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

-- Database schema
CREATE TABLE artist( artistid INTEGER PRIMARY KEY, artistname TEXT
);
CREATE TABLE track( trackid INTEGER, trackname TEXT, trackartist INTEGER DEFAULT 0 REFERENCES artist(artistid) ON DELETE SET DEFAULT
);

sqlite> SELECT * FROM artist;
artistid artistname
-------- -----------------
3 Sammy Davis Jr.

sqlite> SELECT * FROM track;
trackid trackname trackartist
------- ------------- -----------
14 Mr. Bojangles 3

sqlite> -- Deleting the row from the родительская таблица causes the child key
sqlite> -- value of the dependent row to be set to integer value 0. However, this
sqlite> -- value does not correspond to any row in the родительская таблица. Therefore
sqlite> -- the foreign key constraint is violated and an is exception thrown.
sqlite> DELETE FROM artist WHERE artistname = 'Sammy Davis Jr.';
SQL error: foreign key constraint failed

sqlite> -- This time, the value 0 does correspond to a родительская таблица row. And
sqlite> -- so the DELETE statement does not violate the foreign key constraint
sqlite> -- and no exception is thrown.
sqlite> INSERT INTO artist VALUES(0, 'Unknown Artist');
sqlite> DELETE FROM artist WHERE artistname = 'Sammy Davis Jr.';

sqlite> SELECT * FROM artist;
artistid artistname
-------- -----------------
0 Unknown Artist

sqlite> SELECT * FROM track;
trackid trackname trackartist
------- ------------- -----------
14 Mr. Bojangles 0

Те, кто знаком с триггерами SQLite, заметят, что действие ON DELETE SET DEFAULT, продемонстрированное в приведённом выше примере, по своему эффекту аналогично следующему триггеру AFTER DELETE:

CREATE TRIGGER on_delete_set_default AFTER DELETE ON artist BEGIN UPDATE child SET trackartist = 0 WHERE trackartist = old.artistid;
END;

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

1. Выполнить применимые программы триггеров BEFORE,
2. Проверить локальные (не связанные с внешними ключами) ограничения,
3. Обновить или удалить строку в родительской таблице,
4. Выполнить все необходимые действия внешних ключей,
5. Выполнить применимые программы триггеров AFTER.

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

5. Команды CREATE, ALTER и DROP TABLE

В этом разделе описывается, как команды CREATE TABLE, ALTER TABLE и DROP TABLE взаимодействуют с внешними ключами SQLite.

Команда CREATE TABLE работает одинаково независимо от того, включены ли ограничения внешних ключей. Определения родительских ключей ограничений внешних ключей не проверяются при создании таблицы. Ничто не мешает пользователю создать определение внешнего ключа, которое ссылается на несуществующую родительскую таблицу, или на столбцы родительского ключа, которые не существуют или совокупно не связаны ограничением PRIMARY KEY или UNIQUE.

Команда ALTER TABLE работает иначе в двух отношениях, когда ограничения внешних ключей включены:

• Невозможно использовать синтаксис ALTER TABLE ... ADD COLUMN для добавления столбца, включающего предложение REFERENCES, если только значение по умолчанию нового столбца не равно NULL. Попытка сделать это возвращает ошибку.
• Если команда ALTER TABLE ... RENAME TO используется для переименования таблицы, являющейся родительской таблицей одного или нескольких ограничений внешних ключей, определения этих ограничений внешних ключей изменяются так, чтобы ссылаться на родительскую таблицу по её новому имени. Текст дочернего оператора или операторов CREATE TABLE, хранящихся в таблице sqlite_schema, изменяется для отражения нового имени родительской таблицы.

Если ограничения внешних ключей включены в момент подготовки, команда DROP TABLE выполняет неявный DELETE для удаления всех строк из таблицы перед её удалением. Неявный DELETE не вызывает срабатывания каких-либо триггеров SQL, но может инициировать действия внешних ключей или нарушения ограничений. Если немедленное ограничение внешнего ключа нарушено, оператор DROP TABLE завершается с ошибкой и таблица не удаляется.

Если нарушено отложенное ограничение внешнего ключа, то ошибка сообщается, когда пользователь пытается зафиксировать транзакцию, если нарушения ограничений внешних ключей всё ещё существуют в этот момент. Любые ошибки «несоответствия внешнего ключа», возникающие в рамках неявного DELETE, игнорируются.

Цель этих улучшений команд ALTER TABLE и DROP TABLE состоит в том, чтобы гарантировать невозможность их использования для создания базы данных, содержащей нарушения внешних ключей, — по крайней мере пока ограничения внешних ключей включены. Однако из этого правила есть одно исключение.

Если родительский ключ не подпадает под ограничение PRIMARY KEY или UNIQUE, созданное как часть определения родительской таблицы, но подпадает под ограничение UNIQUE благодаря индексу, созданному с помощью команды CREATE INDEX, то дочерняя таблица может быть заполнена без возникновения ошибки «несоответствия внешнего ключа».

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

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

Описанные выше свойства команд DROP TABLE и ALTER TABLE применяются только при включённых внешних ключах. Если пользователь считает их нежелательными, то обходным решением является использование PRAGMA foreign_keys для отключения ограничений внешних ключей перед выполнением команды DROP или ALTER TABLE.

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

6. Ограничения реализации внешних ключей в SQLite

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

Отсутствие поддержки предложения MATCH. Согласно SQL92, предложение MATCH может быть прикреплено к определению составного внешнего ключа для изменения способа обработки значений NULL, встречающихся в дочерних ключах. Если указано MATCH SIMPLE, то дочерний ключ не обязан соответствовать какой-либо строке родительской таблицы, если одно или несколько значений дочернего ключа равны NULL.

Если указано MATCH FULL, то если любое из значений дочернего ключа равно NULL, соответствующая строка в родительской таблице не требуется, но все значения дочернего ключа должны быть NULL.

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

Все ограничения внешних ключей в SQLite обрабатываются так, как если бы было указано MATCH SIMPLE.

Отсутствие поддержки переключения ограничений между отложенным и немедленным режимами. Многие системы позволяют пользователю переключать отдельные ограничения внешних ключей между отложенным и немедленным режимами во время выполнения (например, с помощью команды Oracle SET CONSTRAINT). SQLite не поддерживает это. В SQLite ограничение внешнего ключа постоянно помечается как отложенное или немедленное при его создании.

Ограничение рекурсии для действий внешних ключей. Настройки SQLITE_MAX_TRIGGER_DEPTH и SQLITE_LIMIT_TRIGGER_DEPTH определяют максимально допустимую глубину рекурсии программы триггера. Для целей этих ограничений действия внешних ключей считаются программами триггеров. Настройка PRAGMA recursive_triggers не влияет на работу действий внешних ключей. Невозможно отключить рекурсивные действия внешних ключей.

Внешние ключи не могут пересекать границы схем. То есть в REFERENCES (X.Y) таблица X будет разрешена только в рамках схемы, содержащей предложение REFERENCES.

Ответы на эти вопросы могут быть для вас полезными

Почему ограничения внешних ключей отключены по умолчанию в SQLite?

Это сделано для обратной совместимости с приложениями, написанными до версии 3.6.19. Чтобы включить их, нужно выполнить PRAGMA foreign_keys = ON для каждого соединения с базой данных.

Как проверить, поддерживает ли текущая сборка SQLite внешние ключи?

Выполните PRAGMA foreign_keys. Если команда не возвращает ни одной строки (ни 0, ни 1), значит библиотека скомпилирована с SQLITE_OMIT_FOREIGN_KEY или SQLITE_OMIT_TRIGGER, либо версия SQLite старше 3.6.19.

Чем отличается действие RESTRICT от обычного немедленного ограничения внешнего ключа?

Действие RESTRICT срабатывает немедленно после обновления поля — даже если само ограничение внешнего ключа объявлено как отложенное. Обычное немедленное ограничение проверяется в конце оператора, а отложенное — в момент COMMIT.

Нужно ли создавать индекс на столбцах дочернего ключа?

Формально нет, но на практике это почти всегда необходимо. Без индекса SQLite выполняет полное линейное сканирование дочерней таблицы при каждом удалении или обновлении строки в родительской таблице, что может быть критически медленным на больших объёмах данных.

Что происходит с внешними ключами при выполнении DROP TABLE?

Если ограничения внешних ключей включены, DROP TABLE выполняет неявный DELETE всех строк таблицы. Если при этом нарушается немедленное ограничение внешнего ключа, операция завершается ошибкой и таблица не удаляется. Чтобы обойти это поведение, можно временно отключить ограничения через PRAGMA foreign_keys = OFF перед выполнением DROP TABLE.