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

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

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

SQLite

Содержание

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

2. Включение поддержки внешних ключей

Если желаете использовать внешние ключи в SQLite, то не должны компилировать библиотеку с опциями SQLITE_OMIT_FOREIGN_KEY или SQLITE_OMIT_TRIGGER.

Если определили SQLITE_OMIT_TRIGGER, но не определили SQLITE_OMIT_FOREIGN_KEY, то SQLite будет вести себя также, как до версии 3.6.19, — объявления внешних ключей будут парситься и могут запрашиваться с помощью «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));              -- Ок
CREATE TABLE child2(h, i REFERENCES parent(b));              -- Ок
CREATE TABLE child3(
    j, k, FOREIGN KEY(j, k) REFERENCES parent(c, d)
);                                                           -- Ок
CREATE TABLE child4(l, m REFERENCES parent(e));              -- Ошибка!
CREATE TABLE child5(n, o REFERENCES parent(f));              -- Ошибка!
CREATE TABLE child6(
    p, q, FOREIGN KEY(p, q) REFERENCES parent(b, c)
);                                                           -- Ошибка!
CREATE TABLE child7(r REFERENCES parent(c));                 -- Ошибка!

Внешние ключи, создаваемые на таблицах «child1», «child2» и «child3», создаются без проблем.

Внешний ключ, декларируемый для таблицы «child4», ошибочен, поскольку на столбце родительского ключа хотя и создан индекс, но этот индекс не является UNIQUE. Внешний ключ для таблицы «child5» является ошибкой, поскольку на столбце родительского ключа хотя и создан уникальный индекс, но этот индекс использует другую сортирующую последовательность.

Таблицы «child6» и «child7» некорректны, поскольку обе они хотя и имеют UNIQUE-индексы для своих родительских ключей, но эти ключи не полностью совпадают со столбцами какого-то одного UNIQUE-индекса.

Если схема базы данных содержит ошибочные внешние ключи, выявление которых требует просмотра более чем одного определения таблицы, то такие ошибки не выявляются при создании таблиц. Такие ошибки имеют значение только тогда, когда приложение подготавливает 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
);                                                           -- Ок
CREATE TABLE child9(x REFERENCES parent2);                   -- Ошибка!
CREATE TABLE child10(
    x,y,z, FOREIGN KEY(x,y,z) REFERENCES parent2
);                                                           -- Ошибка!

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

Сообщения о DDL-ошибках внешних ключей выдаются независимо от того, была ли включена поддержка внешних ключей в момент создания таблицы.

Для столбцов дочернего ключа индексы не требуются, но они всегда желательны. Возвращаясь к примеру из Главы 1 заметим, что каждый раз, когда приложение удаляет строку из таблицы «artist» (родительская таблица), для нахождения ссылающихся на нее строк в таблице «track» (дочерняя таблица) выполняется запрос, эквивалентный следующему запросу SELECT:

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.


Назад Вперед
1. Общие сведения об ограничениях внешнего ключа 4. Продвинутые возможности внешних ключей

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


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

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

Последнее редактирование: 2011-09-16 15:51:31

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

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

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

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


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