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

Подписаться через RSS2Email.ru

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

SQLite

Содержание

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

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

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

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

CREATE TABLE album(
  albumartist TEXT,
  albumname TEXT,
  albumcover BINARY,
  PRIMARY KEY(albumartist, albumname)
);

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

В этой системе каждое вхождение в таблицу «song» требует ссылки на строку таблицы «album» и содержит некоторую комбинацию данных об артисте и альбоме.

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

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

Все внешние ключи в SQLite подразделяются на немедленные и отложенные. По умолчанию внешние ключи создаются как немедленные. Все внешние ключи в ранее рассмотренных примерах являются немедленными внешними ключами.

Если запрос модифицирует содержимое базы данных таким образом, что нарушается ограничение немедленного внешнего ключа, то по завершению запроса будет брошено исключение (exception) и результат запроса будет отменен.

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

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

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

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

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

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

NOT DEFERRABLE INITIALLY DEFERRED  -- Немедленный внешний ключ
NOT DEFERRABLE INITIALLY IMMEDIATE -- Немедленный внешний ключ
NOT DEFERRABLE                     -- Немедленный внешний ключ
DEFERRABLE INITIALLY IMMEDIATE     -- Немедленный внешний ключ
DEFERRABLE                         -- Немедленный внешний ключ

Следующий пример иллюстрирует работу отложенного внешнего ключа.

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

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

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

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

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

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


Назад Вперед
3. Необходимые и желательные индексы базы данных 4.3. Операции ON DELETE и ON UPDATE

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


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

Биржа долевых инвестиций SIMEX.

Последнее редактирование: 2011-09-17 01:10:03

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

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

Представиться как     Антибот:
   

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


© 2007-2017, Дмитрий Скоробогатов.
Разрешается воспроизводить, распространять и/или изменять материалы сайта
в соответствии с условиями GNU Free Documentation License,
версии 1.2 или любой более поздней версии, опубликованной FSF,
если только иное не указано в самих материалах.