Поддержка внешнего ключа в SQLite

Этот документ описывает поддержку ограничений внешнего ключа SQL, введенных в SQLite version 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';

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

В вышеупомянутом параграфе термин "равный" означает равный, когда значения сравнены, используя правила определенные здесь. Следующие разъяснения применяются:

2. Предоставление возможности поддержки внешнего ключа

Чтобы использовать ограничения внешнего ключа в SQLite, библиотека должна быть собрана без SQLITE_OMIT_FOREIGN_KEY и SQLITE_OMIT_TRIGGER. Если определяется SQLITE_OMIT_TRIGGER, но SQLITE_OMIT_FOREIGN_KEY нет, SQLite ведет себя как до version 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).

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

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 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 внешнего ключа сообщают независимо от того, позволены ли ограничения внешнего ключа, когда таблица составлена.

Индексы не требуются для детских столбцов ключа, но они почти всегда выгодны. Возвращаясь к примеру выше, каждый раз, когда приложение удаляет строку из таблицы 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 с той же самой комбинацией 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 pragma может использоваться, чтобы временно изменить все ограничения внешнего ключа на отсроченные независимо от того, как они объявлены.

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

-- 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. Действия внешнего ключа подобны триггеру во многих отношениях.

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

Например, добавление "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

Знакомые с триггерами заметят, что "ON DELETE SET DEFAULT", продемонстрированное в примере выше, подобно в действительности следующему AFTER DELETE trigger:

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

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

Есть одно важное различие между действиями внешнего ключа 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 или PRIMARY KEY.

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

Если ограничения внешнего ключа позволены, когда это подготовлено, команда 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. Пределы и неподдержанные особенности

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


WebMoney: WMZ Z294115950220 WMR R409981405661 WME E134003968233	Visa 4274 3200 2453 6495

Обзор