Материал: Graber_Martin_-_SQL_dlya_prostykh_smertnykh_-_2014

Глава 19. Поддержка целостности данных

Рис. 19.1. Внешний ключ для таблицы Customers с родительским ключом

изменения или исключения значений родительского ключа (о чем речь пойдет позже в этой главе).

Как объявить поля внешним ключом

Ограничение FOREIGN КЕУ используется в команде CREATE TABLE (или в ко­ манде ALTER TABLE}, содержащей поле, которое желательно объявить внешним ключом. Указывается имя родительского ключа, на который есть ссылка в ограниче­ нии внешнего ключа. Это ограничение, как и любое другое рассмотренное ранее, раз­ мещается в команде. Подобно большинству ограничений, им может быть таблица или множество столбцов, причем разрешается использовать множество полей как один внешний ключ.

FOREIGN КЕУ как ограничение на таблицу

Представляем синтаксис ограничения на таблицу FOREIGN КЕУ:

FOREIGN КЕУ <список столбцов> REFERENCES <имя таблицы>

[<список столбцов>]

Первый список столбцов представляет собой заключенный в крутлые скобки спи­ сок из одного или более столбцов таблицы, перечисленных через запятую, создавае­ мых или изменяемых этой командой. Предложение REFERENCES задает имя таблицы, содержащей родительский ключ. Это может быть та же самая таблица, кота-

200

Ограниче11ия FOREIGN КЕУ (внешнего ключа)

рая создается или изменяется в текущей команде (подробнее этот вариант рассматри­ вается позже). Второй список столбцов представляет собой заключенный в круглые скобки список столбцов, которые формируют родительский ключ (имена столбцов указываются через запятую). Два списка столбцов должны быть сравнимы:

•Они должны иметь одинаковое количество столбцов.

•Первый, второй, третий и т.д. элемент списка столбцов внешнего ключа долж­

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

Перед вами определение таблицы Customers, в которой snum определено как внеш­ ний ключ, ссылающийся на таблицу Salespeople:

CREATE TABLE Customers

cname char(10),

city char(10),

snum integer,

FOREIGN КЕУ (snum) REFERENCES Salespeople (snum) );

Важный момент, который следует помнить при использовании ALTER TABLE вме­ сто CREATE TABLE для определения ограничений FOREIGN КЕУ, заключается в том,

что значения, присутствующие в настоящий момент во внешнем и родительском клю­

чах, должны находиться в состоянии ссылочной целостности. В противном случае ко­ манда будет отвергнута. Хотя ALTER TABLE и полезна, так как обеспечивает адаптируемость, структурные принципы, подобные ссылочной целостности, следует внедрять в систему на этапе проектирования базы данных.

FOREIGN КЕУ как ограничение на столбец

Версия ограничения FOREIGN КЕУ, как ограничения на столбец, называется огра­ ничением REFERENCES, поскольку реально не содержит слов FOREIGN КЕУ, а со­ держит слово REFERENCES, после которого следуют имена для родительского ключа. Например:

CREATE TABLE Customers

201

Глава 19. Поддержка целоспшости данных

Команда определяет Customers.snum как внешний ключ, родительским ключом ко­ торого является Salespeople.snum. Это эквивалентно следующему ограничению на таблицу:

FOREIGN КЕУ (snum} REFERENCES Salespeople (snum}

Пропуск списков столбцов первичного ключа

Используя ограничение на таблицу или на столбец FOREIGN КЕУ, можно опус­ тить список столбцов родительского ключа, если он имеет ограничение PRIMARY

КЕУ. В случае использования ключей, определенных на множестве полей, порядок пе­

речисления столбцов во внешнем и первичном ключах должен быть соответствую­ щим, и, в любом случае, принцип совместимости между двумя ключами должен поддерживаться. Например, если разместить ограничение PRIMARY КЕУ на поле snum в таблице Salespeople, то можно использовать его как внешний ключ в таблице Customers (аналогично тому, как это было сделано в предыдущем примере) следую­ щим образом:

CREATE TABLE Customers

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

Как ссылочная целостность ограничивает значения

родит,ельского ключа

Управление ссылочной целостностью накладывает ряд ограничений на значения, которые могут быть представлены в полях, объявленных внешним и родительским. Родительский ключ должен быть структурирован так, чтобы была уверенность, что каждое значение внешнего ключа соответствует отдельной строке. Это означает, что оно должно быть уникальным и не должно содержать NULL-значений. Недостаточно, чтобы родительский ключ полностью удовлетворял этим требованиям только в тот момент, когда объявляется внешний ключ. SQL должен гарантировать, что повторяю­ щиеся или NULL-значения не будут введены в родительский ключ. Следовательно, не­ обходимо иметь уверенность, что все поля, которые используются как родительские ключи, имеют либо ограничение PRIMARY КЕУ, либо ограничение UNIQUE, а также ограничение NOT NULL.

202

Ограничения FOREJGN КЕУ (виешнего ключа)

Сравнение PRIMARY с UNIQUE для родительских ключей

При работе с базами данных существенно, что внешние ключи ссылаются только на первичные ключи. Используя внешние ключи, возможно связать их не просто с ро­ дительскими ключами, на которые они ссылаются, а с отдельной строкой таблицы, в которой найден родительский ключ. Сам по себе он не дает никакой информации, ко­ торая не была бы уже представлена внешним ключом.

Например, значимость поля snum как внешнего ключа таблицы Customers, опреде­ ляется связью, которую оно обеспечивает не с самим значением поля snum, на которое ссылается, а с другой информацией таблицы Sa\espeople, такой как имена продавцов,

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

чениями, а взаимодействие между двумя целыми строками рассматриваемых таблиц, основанное на совпадении двух значений. Поле snшn можно использовать для связи любой информации в строке из таблицы Customers с информацией из связанной с ней строки таблицы Salespeople, например: живут ли продавцы и покупатели в одном го­ роде, кто из них имеет более длинное имя, имеет ли продавец, обслуживающий данно­ го покупателя, других покупателей и т.д.

Наиболее логично выбрать первичный ключ в качестве внешнего, поскольку назна­ чение его состоит в уникальной идентификации строки. Для любого внешнего ключа,

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

применяющий первичный ключ той же таблицы с таким же эффектом. Имея внешний

ключ, у которого нет никакого другого назначения кроме связывания строк, и первич­

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

Ограничения внешнего ключа

Внешний ключ может содержать только реально представленные значения или NULL-значения. Попытки ввести в этот ключ другие значения будут отвергнуты. Объ­ являть внешние ключи как NOT NULL нет необходимости, а во многих случаях и не­

желательно. Предположим, при вводе сведений о покупателе еще неизвестно, кто из

продавцов назначен для его обслуживания. Наилучший способ выйти из этой ситуа­ ции состоит во вводе NULL-значений, которые позднее следует заменить на реальные

значения.

203

Глава 19. Поддер:жка целостности данных

Что происходит при выполнении команды обновления

Предположим, все внешние ключи, встроенные в таблицы, объявлены, и для них определены ограничения FOREIGN КЕУ следующим образом:

Использование определений таблицы

Определения таблиц имеют некоторые свойства, требующие обсуждения. Поля cnшn и snum таблицы Orders выбирались в качестве одного внешнего ключа. Следова­ тельно, вы могли быть уверены, что заявка каждого покупателя адресована именно тому продавцу, который указан в таблице Customers. Чтобы создать внешний ключ, нужно

204