|
|
Интернет, компьютеры, софт и прочий Hi-Tech
Избранные доки
Метки (все метки)
hi tech, internet, it, software, интернет, информационные технологии, ит, по, программное обеспечение, софтПоддержка внешних ключей в SQLite
Содержание
- Общие сведения об ограничениях внешнего ключа
- Включение поддержки внешних ключей
- Необходимые и желательные индексы базы данных
- Продвинутые возможности внешних ключей
- Команды CREATE, ALTER и DROP TABLE
- Допустимые пределы и не поддерживаемые возможности
Введение
Этот документ описывает поддержку внешних ключей 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 используется следующая терминология:
- Родительская таблица — это таблица, на которую ссылается внешний ключ. В рассмотренном примере родительской таблицей является таблица «artist». В некоторых книгах и статьях такие таблицы называют еще ссылаемыми таблицами и, возможно, это более корректно, но может привести к путанице.
- Дочерняя таблица — это таблица, к которой применяется ограничение внешнего ключа. Это та таблица, объявление которой содержит выражение REFERENCES. В примерах этой главы используется дочерняя таблица «track». Другие книги и статьи называют такие таблицы ссылающимися таблицами.
- Родительский ключ — это один или несколько столбцов родительской таблицы, на которые ссылается внешний ключ. Как правило, но не обязательно, это есть первичный ключ родительской таблицы. Этот первичный ключ должен быть именованным столбцом родительской таблицы, а не столбцом «rowid».
- Дочерний ключ — это один или несколько столбцов дочерней таблицы, значения которых ограничиваются внешним ключом и которые перечисляются в выражении REFERENCES.
Ограничение внешнего ключа является удовлетворённым, если для каждой строки дочерней таблицы выполнено одно из двух условий: один или больше столбцов дочернего ключа имеют значение NULL, или, если существует такая строка в родительской таблице, что каждый столбец родительского ключа имеет значение, эквивалентное значению соответствующего столбца в дочернем ключе.
Термин «эквивалентное» в предыдущем абзаце используется в том смысле, что сравнение осуществляется с применением правил, описанных здесь. В текущем контексте эти правила означают следующее:
- Когда сравниваются текстовые значения, всегда используется сортирующая последовательность, ассоциированная со столбцом родительского ключа.
- Когда сравниваются значения и родительский ключ является аффинированным, то перед сравнением эта аффинированность применяется также и к значению дочернего ключа.
| Вперед |
| 2. Включение поддержки внешних ключей |
Перевод: Дмитрий Скоробогатов, 14.09.2011
Оригинальный текст доступен по адресу
http://sqlite.org/foreignkeys.html.
Предыдущие публикации:
Последнее редактирование: 2011-09-15 11:38:11
Метки материала: sqlite, поддержка внешних ключей, документация для программиста, по, бесплатное по, it, базы данных, софт, sql, программирование, soft, software, разработка по, программное обеспечение, db, спо, информационные технологии, ит, программное обеспечение по