![]() Интернет, компьютеры, софт и прочий Hi-Tech | ||||||
Избранные докиМетки (все метки)hi tech, internet, it, software, интернет, информационные технологии, ит, по, программное обеспечение, софт
Подписаться через RSS2Email.ru
|
Поддержка внешних ключей в SQLite![]() Содержание
ВведениеЭтот документ описывает поддержку внешних ключей SQL, реализованных в SQLite версии 3.6.19. Первая глава посвящена общей концепции внешних ключей в SQLite, объясняет ее с помощью примеров и терминологических определений, необходимых для понимания данного документа. Вторая глава пошагово описывает, что необходимо сделать для включения поддержки внешних ключей в SQLite (по умолчанию они отключены). Следующая глава (третья) описывает, какие индексы должен создавать пользователь для использования внешних ключей, и какие следует создать для повышения эффективности работы внешнего ключа. Глава 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 -- Должен ссылаться на 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> -- Следующий запрос приведет к ошибке, поскольку значению, sqlite> -- вставляемому в столбец trackartist (3), не соответствует sqlite> -- записи в таблице artist. sqlite> INSERT INTO track VALUES(14, 'Mr. Bojangles', 3); SQL error: foreign key constraint failed sqlite> -- Следующий запрос выполнится успешно, так как в sqlite> -- trackartist вставляется NULL. В этом случае наличие sqlite> -- соответствующей строки в таблице artist не требуется. sqlite> INSERT INTO track VALUES(14, 'Mr. Bojangles', NULL); sqlite> -- Попытка изменить поле trackartist этой вставленной записи sqlite> -- окончится неудачей, поскольку для нового значения sqlite> -- trackartist (3) нет соответствующей строки в таблице sqlite> -- artist. sqlite> UPDATE track SET trackartist = 3 WHERE trackname = 'Mr. Bojangles'; SQL error: foreign key constraint failed sqlite> -- Вставляем требуемую строку в таблицу artist. Это делает sqlite> -- возможным изменение строки, вставленной в track, путем sqlite> -- установления поля trackartist в значение 3 (поскольку sqlite> -- соответствующая строка в таблице artist теперь sqlite> -- существует). sqlite> INSERT INTO artist VALUES(3, 'Sammy Davis Jr.'); sqlite> UPDATE track SET trackartist = 3 WHERE trackname = 'Mr. Bojangles'; sqlite> -- Итак, "Sammy Davis Jr." (artistid = 3) теперь добавлен в sqlite> -- базу данных и стало возможным добавить новый трек этого sqlite> -- артиста без нарушения ограничений внешнего ключа: sqlite> INSERT INTO track VALUES(15, 'Boogie Woogie', 3); Дальше вы можете убедиться, что невозможно так манипулировать базой данных, удалять и изменять строки таблицы «artist», чтобы нарушалось ограничение внешнего ключа: sqlite> -- Попытка удалить данные об артисте "Frank Sinatra" sqlite> -- приведет к ошибке, поскольку таблица track содержит sqlite> -- запись, ссылающуюся на артиста. sqlite> DELETE FROM artist WHERE artistname = 'Frank Sinatra'; SQL error: foreign key constraint failed sqlite> -- Удаляем все записи из таблицы track, которые ссылаются на sqlite> -- артиста "Frank Sinatra". Только после этого становится sqlite> -- возможным удалить этого артиста. sqlite> DELETE FROM track WHERE trackname = 'My Way'; sqlite> DELETE FROM artist WHERE artistname = 'Frank Sinatra'; sqlite> -- Попытка изменить artistid строки из таблицы artist sqlite> -- заканчивается неудачей, если в таблице track имеются sqlite> -- записи, ссылающиеся на данную строку из artist. sqlite> UPDATE artist SET artistid=4 WHERE artistname = 'Dean Martin'; SQL error: foreign key constraint failed sqlite> -- Теперь удалим все записи, ссылающиеся на одну из строк sqlite> -- таблицы artist. Это делает возможным изменение artistid sqlite> -- этой строки. sqlite> DELETE FROM track WHERE trackname IN('That''s Amore', 'Christmas Blues'); sqlite> UPDATE artist SET artistid=4 WHERE artistname = 'Dean Martin'; В SQLite используется следующая терминология:
Ограничение внешнего ключа является удовлетворённым, если для каждой строки дочерней таблицы выполнено одно из двух условий: один или больше столбцов дочернего ключа имеют значение NULL, или, если существует такая строка в родительской таблице, что каждый столбец родительского ключа имеет значение, эквивалентное значению соответствующего столбца в дочернем ключе. Термин «эквивалентное» в предыдущем абзаце используется в том смысле, что сравнение осуществляется с применением правил, описанных здесь. В текущем контексте эти правила означают следующее:
Перевод: Дмитрий Скоробогатов, 14.09.2011 Предыдущие публикации: Последнее редактирование: 2011-09-15 11:38:11 Метки материала: sqlite, поддержка внешних ключей, документация для программиста, по, бесплатное по, it, базы данных, софт, sql, программирование, soft, software, разработка по, программное обеспечение, db, спо, информационные технологии, ит, программное обеспечение по Оставьте, пожалуйста, свой комментарий к публикации |
© 2007-2019, Дмитрий Скоробогатов.
Разрешается воспроизводить, распространять и/или изменять материалы сайта
в соответствии с условиями GNU Free Documentation License,
версии 1.2 или любой более поздней версии, опубликованной FSF,
если только иное не указано в самих материалах.