Поддержка внешних ключей в SQLite
Содержание
Обзор
Данный документ описывает поддержку ограничений внешних ключей SQL, введённую в SQLite версии 3.6.19 (2009-10-14).
Первый раздел вводит понятие внешнего ключа SQL на примере и определяет терминологию, используемую в остальной части документа. Раздел 2 описывает шаги, которые приложение должно предпринять для включения ограничений внешних ключей в SQLite (по умолчанию они отключены). Следующий раздел, раздел 3, описывает индексы, которые пользователь должен создать для использования ограничений внешних ключей, и те, которые следует создать для эффективной работы ограничений внешних ключей. Раздел 4 описывает расширенные функции, связанные с внешними ключами, поддерживаемые SQLite, а раздел 5 описывает способ расширения команд ALTER и DROP TABLE для поддержки ограничений внешних ключей. Наконец, раздел 6 перечисляет отсутствующие функции и ограничения текущей реализации.
В данном документе не содержится полного описания синтаксиса создания ограничений внешних ключей в SQLite. Это можно найти в документации к команде CREATE TABLE.
1. Введение во внешние ключи
Ограничения внешних ключей SQL используются для принудительного установления взаимосвязей «существует» между таблицами. Например, рассмотрим схему базы данных, созданную с помощью следующих команд SQL:
CREATE TABLE artist( artistid INTEGER PRIMARY KEY, artistname TEXT ); CREATE TABLE track( trackid INTEGER, trackname TEXT, trackartist INTEGER -- Must map to an artist.artistid! );
Приложения, использующие эту базу данных, имеют право предполагать, что для каждой строки в таблице track существует соответствующая строка в таблице artist. В конце концов, это сказано в комментарии к объявлению. К сожалению, если пользователь редактирует базу данных с помощью внешнего инструмента или если в приложении есть ошибка, в таблицу track могут быть вставлены строки, которые не соответствуют никакой строке в таблице artist. Или строки могут быть удалены из таблицы artist, оставив сироты строки в таблице track, которые не соответствуют ни одной из оставшихся строк в таблице artist. Это может привести к неправильной работе приложения или приложений, или по крайней мере затруднит кодирование приложения.
Одним из решений является добавление ограничения внешнего ключа SQL в схему базы данных для принудительного установления взаимосвязи между таблицами artist и track. Для этого определение внешнего ключа можно добавить, изменив объявление таблицы track на следующее:
CREATE TABLE track( trackid INTEGER, trackname TEXT, trackartist INTEGER, FOREIGN KEY(trackartist) REFERENCES artist(artistid) );
Таким образом, ограничение выполняется SQLite. Попытка вставить строку в таблицу track, которая не соответствует никакой строке в таблице artist, завершится неудачей, как и попытка удалить строку из таблицы artist, когда существуют зависимые строки в таблице track. Есть одно исключение: если столбец внешнего ключа в таблице track имеет значение NULL, то соответствующая запись в таблице artist не требуется. В 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> -- This fails because the value inserted into the trackartist column (3) sqlite> -- does not correspond to row in the artist table. sqlite> INSERT INTO track VALUES(14, 'Mr. Bojangles', 3); SQL error: foreign key constraint failed sqlite> -- This succeeds because a NULL is inserted into trackartist. A sqlite> -- corresponding row in the artist table is not required in this case. sqlite> INSERT INTO track VALUES(14, 'Mr. Bojangles', NULL); sqlite> -- Trying to modify the trackartist field of the record after it has sqlite> -- been inserted does not work either, since the new value of trackartist (3) sqlite> -- Still does not correspond to any row in the artist table. sqlite> UPDATE track SET trackartist = 3 WHERE trackname = 'Mr. Bojangles'; SQL error: foreign key constraint failed sqlite> -- Insert the required row into the artist table. It is then possible to sqlite> -- update the inserted row to set trackartist to 3 (since a corresponding sqlite> -- row in the artist table now exists). sqlite> INSERT INTO artist VALUES(3, 'Sammy Davis Jr.'); sqlite> UPDATE track SET trackartist = 3 WHERE trackname = 'Mr. Bojangles'; sqlite> -- Now that "Sammy Davis Jr." (artistid = 3) has been added to the database, sqlite> -- it is possible to INSERT new tracks using this artist without violating sqlite> -- the foreign key constraint: sqlite> INSERT INTO track VALUES(15, 'Boogie Woogie', 3);
Как можно ожидать, невозможно изменить состояние базы данных таким образом, чтобы нарушить ограничение внешнего ключа, удаляя или обновляя строки в таблице artist:
sqlite> -- Attempting to delete the artist record for "Frank Sinatra" fails, since
sqlite> -- the track table contains a row that refer to it.
sqlite> DELETE FROM artist WHERE artistname = 'Frank Sinatra';
SQL error: foreign key constraint failed
sqlite> -- Delete all the records from the track table that refer to the artist
sqlite> -- "Frank Sinatra". Only then is it possible to delete the artist.
sqlite> DELETE FROM track WHERE trackname = 'My Way';
sqlite> DELETE FROM artist WHERE artistname = 'Frank Sinatra';
sqlite> -- Try to update the artistid of a row in the artist table while there
sqlite> -- exists records in the track table that refer to it.
sqlite> UPDATE artist SET artistid=4 WHERE artistname = 'Dean Martin';
SQL error: foreign key constraint failed
sqlite> -- Once all the records that refer to a row in the artist table have
sqlite> -- been deleted, it is possible to modify the artistid of the row.
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. В других книгах и статьях это называется ссылочной таблицей.
Ключ родительской таблицы — это столбец или набор столбцов в родительской таблице, к которому относится ограничение внешнего ключа. Обычно, но не всегда, это первичный ключ родительской таблицы. Ключ родительской таблицы должен быть именованным столбцом или столбцами в родительской таблице, а не rowid.
Ключ дочерней таблицы — это столбец или набор столбцов в дочерней таблице, ограниченный ограничением внешнего ключа и содержащий предложение REFERENCES.
Ограничение внешнего ключа выполняется, если для каждой строки в дочерней таблице либо один или несколько столбцов ключа дочерней таблицы имеют значение NULL, либо существует строка в родительской таблице, для которой каждый столбец ключа родительской таблицы содержит значение, равное значению в соответствующем столбце ключа дочерней таблицы.
В абзаце выше термин «равно» означает «равно» при сравнении значений в соответствии с правилами, указанными здесь. Применяются следующие пояснения:
При сравнении текстовых значений всегда используется сортировочная последовательность (collating sequence), связанная со столбцом ключа родительской таблицы.
При сравнении значений, если столбец ключа родительской таблицы имеет тип данных, то этот тип данных применяется к значению ключа дочерней таблицы перед выполнением сравнения.
2. Включение поддержки внешних ключей
Для использования ограничений внешних ключей в 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, является ошибкой, так как, хотя столбец ключа родительской таблицы и индексирован, индекс не является уникальным. Ограничение внешнего ключа для таблицы child5 является ошибкой, так как, хотя столбец ключа родительской таблицы и имеет уникальный индекс, индекс использует другую сортировочную последовательность. Таблицы child6 и child7 некорректны, так как, хотя обе имеют уникальные индексы на своих родительских ключах, ключи не являются точным совпадением со столбцами одного уникального индекса.
Если в схеме базы данных есть ошибки внешних ключей, требующие анализа более одной таблицы для определения, то эти ошибки не обнаруживаются при создании таблиц. Вместо этого такие ошибки препятствуют приготовлению приложению SQL-запросов, которые изменяют содержимое дочерних или родительских таблиц с использованием внешних ключей. Ошибки, сообщаемые при изменении содержимого, являются «ошибками DML», а ошибки, сообщаемые при изменении схемы, являются «ошибками DDL». Другими словами, неправильно настроенные ограничения внешних ключей, требующие анализа и дочерней, и родительской таблицы, являются ошибками DML. Сообщение об ошибке для ошибок DML внешнего ключа обычно является «несоответствием внешнего ключа», но также может быть «такой таблицы нет», если родительская таблица не существует. Ошибки 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 делает вывод, что удаление строки из родительской таблицы нарушит ограничение внешнего ключа и возвращает ошибку. Аналогичные запросы могут выполняться, если содержимое родительского ключа изменяется или в родительскую таблицу вставляется новая строка. Если эти запросы не могут использовать индекс, они вынуждены выполнять линейный поиск всей дочерней таблицы. В нетривиальной базе данных это может быть слишком дорого.
Таким образом, в большинстве реальных систем следует создавать индекс по столбцам дочернего ключа каждого ограничения внешнего ключа. Индекс дочернего ключа не должен быть (и обычно не будет) уникальным индексом. Возвращаясь ещё раз к примеру в разделе 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) );
В этой системе каждая запись в таблице песен должна соответствовать записи в таблице альбомов с тем же сочетанием исполнителя и альбома.
Родительский и дочерний ключи должны иметь одинаковую мощность. В SQLite, если какой-либо из столбцов дочернего ключа (в данном случае songartist и songalbum) имеет значение NULL, то нет требования к наличию соответствующей строки в родительской таблице.
4.2. Отложенные ограничения внешнего ключа
Каждое ограничение внешнего ключа в SQLite классифицируется как немедленное или отложенное. По умолчанию ограничения внешних ключей немедленные. Все примеры ограничений внешних ключей, представленные до сих пор, являются примерами немедленных ограничений внешних ключей.
Если оператор изменяет содержимое базы данных таким образом, что немедленное ограничение внешнего ключа нарушается по завершении оператора, возникает исключение, и результаты оператора отменяются. В отличие от этого, если оператор изменяет содержимое базы данных таким образом, что нарушается отложенное ограничение внешнего ключа, нарушение не сообщается немедленно. Отложенные ограничения внешних ключей проверяются только тогда, когда транзакция пытается выполнить COMMIT. Пока у пользователя открыта транзакция, базе данных разрешается находиться в состоянии, нарушающем любое количество отложенных ограничений внешнего ключа. Однако COMMIT завершится неудачно, если ограничения внешних ключей останутся нарушенными.
Если текущий оператор не находится внутри явной транзакции (блок BEGIN/COMMIT/ROLLBACK), то неявная транзакция подтверждается как только оператор завершит выполнение. В этом случае отложенные ограничения ведут себя так же, как немедленные ограничения.
Чтобы пометить ограничение внешнего ключа как отложенное, в его объявлении должно быть указано следующее условие:
DEFERRABLE INITIALLY DEFERRED -- A deferred foreign key constraint
Полный синтаксис указания ограничений внешних ключей доступен в документации к CREATE TABLE. Замена вышеуказанной фразы любой из следующих создаёт немедленное ограничение внешнего ключа.
NOT DEFERRABLE INITIALLY DEFERRED -- An immediate foreign key constraint NOT DEFERRABLE INITIALLY IMMEDIATE -- An immediate foreign key constraint NOT DEFERRABLE -- An immediate foreign key constraint DEFERRABLE INITIALLY IMMEDIATE -- An immediate foreign key constraint DEFERRABLE -- An immediate foreign key constraint
Предикат defer_foreign_keys можно использовать для временного изменения всех ограничений внешних ключей на отложенные, независимо от того, как они объявлены.
Следующий пример иллюстрирует эффект использования отложенного ограничения внешнего ключа.
-- Database schema. Both tables are initially empty. 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> -- If the foreign key constraint were immediate, this INSERT would sqlite3> -- cause an error (since as there is no row in table artist with sqlite3> -- artistid=5). But as the constraint is deferred and there is an sqlite3> -- open transaction, no error occurs. sqlite3> BEGIN; sqlite3> INSERT INTO track VALUES(1, 'White Christmas', 5); sqlite3> -- The following COMMIT fails, as the database is in a state that sqlite3> -- does not satisfy the deferred foreign key constraint. The sqlite3> -- transaction remains open. sqlite3> COMMIT; SQL error: foreign key constraint failed sqlite3> -- After inserting a row into the artist table with artistid=5, the sqlite3> -- deferred foreign key constraint is satisfied. It is then possible sqlite3> -- to commit the transaction without error. sqlite3> INSERT INTO artist VALUES(5, 'Bing Crosby'); sqlite3> COMMIT;
Транзакция с вложенной точкой сохранения может быть отменена, пока база данных находится в состоянии, не удовлетворяющем отложенному ограничению внешнего ключа. Точка сохранения транзакции (точка сохранения, которая не является вложенной и которая была открыта, когда не было открытой транзакции), с другой стороны, подчиняется тем же ограничениям, что и COMMIT — попытка её отмены, пока база данных находится в таком состоянии, завершится неудачей.
Если оператор COMMIT (или отмена транзакционной точки сохранения) завершается неудачей, потому что база данных в настоящее время находится в состоянии, нарушающем отложенное ограничение внешнего ключа, и в настоящее время существуют вложенные точки сохранения, вложенные точки сохранения остаются открытыми.
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> 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> -- 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 child table, 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 parent table 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 parent table. 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 parent table 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;
Всякий раз, когда удаляется строка в родительской таблице ограничения внешнего ключа или когда значения, хранящиеся в столбце или столбцах родительского ключа, изменяются, логическая последовательность событий такова:
- Выполнить соответствующие программы триггеров BEFORE,
- Проверить локальные ограничения (не связанные с внешним ключом),
- Обновить или удалить строку в родительской таблице,
- Выполнить необходимые действия внешнего ключа,
- Выполнить соответствующие программы триггеров AFTER.
Существует одно важное различие между действиями внешнего ключа ON UPDATE и SQL-триггерами. Действие ON UPDATE выполняется только в том случае, если значения родительского ключа изменяются так, что новые значения родительского ключа не равны старым. Например:
-- Database schema CREATE TABLE parent(x PRIMARY KEY); CREATE TABLE child(y REFERENCES parent ON UPDATE SET NULL); sqlite> SELECT * FROM parent; x ---- key sqlite> SELECT * FROM child; y ---- key sqlite> -- Since the following UPDATE statement does not actually modify sqlite> -- the parent key value, the ON UPDATE action is not performed and sqlite> -- the child key value is not set to NULL. sqlite> UPDATE parent SET x = 'key'; sqlite> SELECT IFNULL(y, 'null') FROM child; y ---- key sqlite> -- This time, since the UPDATE statement does modify the parent key sqlite> -- value, the ON UPDATE action is performed and the child key is set sqlite> -- to NULL. sqlite> UPDATE parent SET x = 'key2'; sqlite> SELECT IFNULL(y, 'null') FROM child; y ---- null
5. Команды CREATE, ALTER и DROP TABLE
В этом разделе описано взаимодействие команд CREATE TABLE, ALTER TABLE и DROP TABLE с внешними ключами SQLite.
Команда CREATE TABLE работает одинаково независимо от того, включены ли ограничения внешних ключей. Определения родительского ключа ограничений внешних ключей не проверяются при создании таблицы. Ничто не мешает пользователю создать определение внешнего ключа, которое ссылается на родительскую таблицу, которая не существует, или на столбцы родительского ключа, которые не существуют или не объединены первичным ключом или ограничением 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. Ограничения и неподдерживаемые функции
В этом разделе перечислены некоторые ограничения и пропущенные функции, которые не упомянуты в других местах.
-
Нет поддержки предложения MATCH. Согласно SQL92, предложение MATCH может быть добавлено к определению составного внешнего ключа для изменения способа обработки значений NULL, встречающихся в дочерних ключах. Если указано «MATCH SIMPLE», то дочерний ключ не обязан соответствовать какой-либо строке родительской таблицы, если одно или несколько значений дочернего ключа равны NULL. Если указано «MATCH FULL», то если какое-либо из значений дочернего ключа равно NULL, соответствующая строка в родительской таблице не требуется, но все значения дочернего ключа должны быть NULL. Наконец, если ограничение внешнего ключа объявлено как «MATCH PARTIAL» и одно из значений дочернего ключа равно NULL, должна существовать хотя бы одна строка в родительской таблице, для которой значения дочернего ключа, отличные от 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 is in the Public Domain.
https://sqlite.org/foreignkeys.html
