Интернет, компьютеры, софт и прочий Hi-Tech

Поддержка внешних ключей в SQLite

Содержание

1. Общие сведения об ограничениях внешнего ключа
2. Включение поддержки внешних ключей
3. Необходимые и желательные индексы базы данных
4. Продвинутые возможности внешних ключей
   1. Составные внешние ключи
   2. Отложенные внешние ключи
   3. Операции ON DELETE и ON UPDATE
5. Команды CREATE, ALTER и DROP TABLE
6. Допустимые пределы и не поддерживаемые возможности

4. Продвинутые возможности внешних ключей

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 запускается тогда, когда изменяется поле записи, а не тогда, когда завершается текущий запрос, как в случае немедленного ограничения, и не тогда, когда завершается текущая транзакция, как в случае отложенного ограничения. Даже если внешний ключ был заявлен как отложенный, если его действием был объявлен RESTRICT, то SQLite будет выдавать сообщение об ошибке немедленно, как только удаляется или изменяется родительский ключ, от которого зависят дочерние ключи.
• SET NULL: если действие сконфигурировано как «SET NULL», то при удалении родительского ключа (для ON DELETE SET NULL) или его изменении (для ON UPDATE SET NULL) столбцы дочернего ключа будут устанавливаться в значение NULL во всех строках дочерней таблицы, которые ссылаются на удаляемую/изменяемую строку родительской таблицы.
• SET DEFAULT: действие «SET DEFAULT» похоже на SET NULL за тем исключением, что значение каждого столбца дочернего ключа устанавливается не в NULL а в значение по умолчанию для данного столбца. О том, как столбцам назначаются значения по умолчанию, можно узнать в подробной документации по CREATE TABLE.
• CASCADE: действие «CASCADE» распространяет операции удаления и изменения родительского ключа на зависящие от него дочерние ключи. Для действия ON DELETE CASCADE это выражается в том, что каждая строка дочерней таблицы, которая ассоциирована с удаляемой родительской строкой, также будет удалена. Для действия ON UPDATE CASCADE это выражается в том, что значения, сохранённые в зависящем дочернем ключе, будут заменены на новые значения родительского ключа.

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

-- Схема базы данных
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> -- Обновляем поле artistid в записи для артиста
sqlite> -- "Dean Martin". Обычно это не удается из-за ограничения
sqlite> -- внешнего ключа, так как в таблице track существуют две
sqlite> -- зависимые записи от изменяемой. Однако выражение
sqlite> -- ON UPDATE CASCADE в объявлении внешнего ключа приводит к
sqlite> -- "каскадному" обновлению дочерней таблицы, что исключает
sqlite> -- нарушение ограничения внешнего ключа.
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, но в родительской таблице отсутствует строка, соответствующая умолчальным значениям столбцов дочернего ключа, то удаление родительского ключа, для которого существуют зависимые дочерние ключи, приведет к нарушению внешнего ключа. Например:

-- Схема базы данных
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> -- Удаление строки из родительской таблицы приводит к
sqlite> -- установлению значения дочернего ключа зависимой строки в
sqlite> -- целочисленное значение 0. Однако для этого значения нет
sqlite> -- соответствующей строки в родительской таблице. Тем самым
sqlite> -- нарушается ограничение внешнего ключа и выдается
sqlite> -- сообщение об ошибке.
sqlite> DELETE FROM artist WHERE artistname = 'Sammy Davis Jr.';
SQL error: foreign key constraint failed

sqlite> -- В данном случае значение 0 имеет соответствующую строку в
sqlite> -- родительской таблице, а команда DELETE не нарушает
sqlite> -- ограничение внешнего ключа, выполняется без ошибки.
sqlite> INSERT INTO artist VALUES(0, 'Unknown Artist');
sqlite> DELETE FROM artist WHERE artistname = 'Sammy Davis Jr.';

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

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

Похоже на те действия SQLite, которые помечены как ON DELETE SET DEFAULT и продемонстрированы в примере выше, работает следующий триггер «AFTER DELETE»:

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

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

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

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

-- Схема базы данных
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> -- Поскольку следующая команда UPDATE фактически не изменяет
sqlite> -- значение родительского ключа, то и действие ON UPDATE не
sqlite> -- срабатывает, и значение дочернего ключа не
sqlite> -- устанавливается в NULL.
sqlite> UPDATE parent SET x = 'key';
sqlite> SELECT IFNULL(y, 'null') FROM child;
y
----
key

sqlite> -- Поскольку теперь команда UPDATE изменяет значение
sqlite> -- родительского ключа, то срабатывает действие на ON UPDATE
sqlite> -- и дочерний ключ устанавливается в NULL.
sqlite> UPDATE parent SET x = 'key2';
sqlite> SELECT IFNULL(y, 'null') FROM child;
y
----
null

Перевод: Дмитрий Скоробогатов, 18.09.2011
Оригинальный текст доступен по адресу http://sqlite.org/foreignkeys.html.

Предыдущие публикации:

Последнее редактирование: 2011-09-18 01:50:50

Метки материала: delete, update, sqlite, on delete, on update, поддержка внешних ключей, документация для программиста, it, базы данных, sql, программирование, software, разработка по, программное обеспечение, db, информационные технологии, ит, программное обеспечение по

Оставьте, пожалуйста, свой комментарий к публикации

Представиться как

Антибот:

Просьба не постить мусор. Если вы хотите потестить xBB, воспользуйтесь кнопкой предварительного просмотра на панели инструментов xBBEditor-а.