Поддержка внешних ключей 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 any 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 refers 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 либо в родительской таблице существует строка, в которой значение каждого столбца родительского ключа равно значению соответствующего столбца дочернего ключа.
В предыдущем абзаце термин «равно» означает равенство при сравнении значений по правилам, описанным здесь. Применяются следующие уточнения:
При сравнении текстовых значений всегда используется последовательность сортировки, связанная со столбцом родительского ключа.
При сравнении значений, если для столбца родительского ключа задана аффинность, она применяется к значению дочернего ключа до выполнения сравнения.
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 или иметь уникальный индекс. Если для столбцов родительского ключа создан уникальный индекс, он должен использовать последовательности сортировки, указанные в операторе 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, связанной с внешним ключом, обычно имеет вид "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 делает вывод, что удаление строки из родительской таблицы нарушит ограничение внешнего ключа, и возвращает ошибку. Аналогичные запросы могут выполняться при изменении содержимого родительского ключа или вставке новой строки в родительскую таблицу. Если для этих запросов нельзя использовать индекс, приходится выполнять линейное сканирование всей дочерней таблицы. В нетривиальной базе данных это может оказаться неприемлемо дорогостоящим.
Поэтому в большинстве реальных систем для столбцов дочернего ключа каждого ограничения внешнего ключа следует создавать индекс. Индекс дочернего ключа не обязан быть уникальным (и обычно таковым не является). Снова вернёмся к примеру из раздела 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, содержащей те же значения столбцов artist и album.
Родительский и дочерний ключи должны иметь одинаковую размерность. В 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;
Вложенную транзакцию с точкой сохранения можно завершить с помощью RELEASE, даже если база данных находится в состоянии, нарушающем отложенное ограничение внешнего ключа. На транзакционную точку сохранения (не вложенную точку сохранения, открытую при отсутствии активной транзакции), напротив, распространяются те же ограничения, что и на COMMIT: попытка выполнить RELEASE в таком состоянии завершится ошибкой.
Если оператор COMMIT (или RELEASE транзакционной точки сохранения SAVEPOINT) завершается ошибкой, поскольку база данных находится в состоянии, нарушающем отложенное ограничение внешнего ключа, и в данный момент открыты вложенные точки сохранения, они остаются открытыми.
4.3. Действия ON DELETE и ON UPDATE
Предложения ON DELETE и ON UPDATE внешнего ключа задают действия, выполняемые при удалении строк из родительской таблицы (ON DELETE) или изменении значений родительского ключа в существующих строках (ON UPDATE). Для одного ограничения внешнего ключа можно задать разные действия ON DELETE и ON UPDATE. Во многих отношениях действия внешнего ключа похожи на триггеры.
Для каждого внешнего ключа в базе данных SQLite действие ON DELETE и ON UPDATE выбирается из следующих вариантов: "NO ACTION", "RESTRICT", "SET NULL", "SET DEFAULT" или "CASCADE". Если действие явно не указано, по умолчанию используется "NO ACTION".
NO ACTION: настройка "NO ACTION" означает именно то, что написано: при изменении или удалении родительского ключа из базы данных никаких специальных действий не выполняется.
RESTRICT: действие "RESTRICT" запрещает приложению удалять (для ON DELETE RESTRICT) или изменять (для ON UPDATE 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 работает одинаково независимо от того, включены ли ограничения внешнего ключа. Определения родительских ключей ограничений внешнего ключа не проверяются при создании таблицы. Ничто не мешает пользователю создать определение внешнего ключа, ссылающееся на несуществующую родительскую таблицу или на несуществующие столбцы родительского ключа, которые в совокупности не подпадают под ограничение 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 завершается ошибкой, и таблица не удаляется. Если нарушено отложенное ограничение внешнего ключа, ошибка сообщается при попытке пользователя зафиксировать транзакцию, если к этому моменту нарушение сохраняется. Все ошибки "foreign key mismatch", возникшие в результате неявного DELETE, игнорируются.
Цель этих изменений команд ALTER TABLE и DROP TABLE — не допустить их использования для создания базы данных с нарушениями внешних ключей, по крайней мере пока ограничения внешнего ключа включены. Однако есть одно исключение. Если родительский ключ не подпадает под ограничение PRIMARY KEY или UNIQUE, созданное в определении родительской таблицы, но подпадает под ограничение UNIQUE благодаря индексу, созданному командой CREATE INDEX, дочернюю таблицу можно заполнить данными, не вызывая ошибки "foreign key mismatch". Если уникальный индекс удалить из схемы базы данных, а затем удалить саму родительскую таблицу, ошибка не сообщается. Однако база данных может оказаться в состоянии, при котором дочерняя таблица ограничения внешнего ключа содержит строки, не ссылающиеся ни на одну строку родительской таблицы. Этой ситуации можно избежать, если все родительские ключи в схеме базы данных ограничены PRIMARY KEY или UNIQUE, добавленными в определение родительской таблицы, а не внешними уникальными индексами.
Описанные выше свойства команд DROP TABLE и ALTER TABLE действуют только при включённых внешних ключах. Если пользователь считает такое поведение нежелательным, перед выполнением команды DROP или ALTER TABLE можно отключить ограничения внешнего ключа с помощью PRAGMA foreign_keys. Разумеется, пока ограничения внешнего ключа отключены, ничто не мешает пользователю нарушить их и тем самым создать внутренне несогласованную базу данных.
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.
Последнее обновление этой страницы: 2026-03-20 12:37:00Z
SQLite is in the Public Domain.
https://sqlite.org/foreignkeys.html
