СМЫСЛ ВНЕШНЕГО И РОДИТЕЛЬСКОГО КЛЮЧЕЙ
Когда поле - является внешним ключом, оно определенным образом связано
с таблицей на которую он ссылается. Вы, фактически, говорите - " каждое
значение в этом поле ( внешнем ключе ) непосредственно привязано к зна-
чению в другом поле ( родительском ключе )." Каждое значение ( каждая
строка ) внешнего ключа должно недвусмысленно ссылаться к одному и
только этому значению (строке) родительского ключа.
Если это так, то фактически ваша система, как говорится, будет в состоянии
справочной целостности.
Вы можете увидеть это на примере. Внешний ключ snum в таблице Заказ-
чиков имеет значение 1001 для строк Hoffman и Clemens.
Предположим что мы имели две строки в таблице Продавцов со значением
в поле snum = 1001.
Как мы узнаем, к которому из двух продавцов были назначены заказчики
Hoffman и Clemens ? Аналогично, если нет никаких таких строк в таблице
Продавцов, мы получим Hoffman и Clemens назначенными к продавцу
которого не существует!
Понятно, что каждое значение во внешнем ключе должно быть представ-
лено один, и только один раз, в родительском ключе.
Фактически, данное значение внешнего ключа может ссылаться только
к одному значению родительского ключа не предполагая обратной возможности: т.е. любое число внешних ключей может ссылать к единственному
значению родительского ключа. Вы можете увидеть это в типовых таблицах
наших примеров. И Hoffman и Clemens назначены к Peel, так что оба их
значения внешнего ключа совпадают с одним и тем же родительским ключом, что очень хорошо. Значение внешнего ключа должно ссылаться только
к одному значению родительского ключа, зато значение родительского ключа может ссылаться с помощью любого количества значений внешнего
ключа.
В качестве иллюстрации, значения внешнего ключа из таблицы Заказчиков,
совпавшие с их родительским ключом в Продавцов таблице, показываются
в Рисунке 19.1. Для удобства мы не учитывали пол не относящиеся к этому
примеру.
ВНЕШНИЙ КЛЮЧ КАК ОГРАНИЧЕНИЕ ТАБЛИЦЫ
Синтаксис ограничения таблицы FOREIGN KEY:
FOREIGN KEY REFERENCES
[ ]
Первый список столбцов - это список из одного или более столбцов таблицы,
которые отделены запятыми и будут созданы или изменены этой командой.
Pktable - это таблица содержащая родительский ключ. Она может быть таблицей, которая создается или изменяется текущей командой. Второй список
столбцов - это список столбцов которые будут составлять родительский ключ.
Списки двух столбцов должны быть совместимы, т.е.:
* Они должны иметь одинаковое число столбцов.
* В данной последовательности, первый, второй, третий, и т.д.,
столбцы списка столбцов внешнего ключа, должны иметь одинаковые
типы данных и размеры, что и первый, второй, третий, и т.д., столбцы
списка столбцов родительского ключа.
Столбцы в списках обоих столбцов не должны иметь одинаковых имен,
хотя мы и использовали такой способ в наших примерах чтобы делать
связь более понятной.
Создадим таблицу Заказчиков с полем snum определенным в качестве
внешнего ключа ссылающегося на таблицу Продавцов:
CREATE TABLE Customers
( cnum integer NOT NULL PRIMARY KEY
cname char(10),
city char(10),
snum integer,
FOREIGN KEY (snum) REFERENCES Salespeople
( snum );
Имейте в виду, что при использовании ALTER TABLE вместо CREATE TABLE,
для применения ограничения FOREIGN KEY, значения которые Вы указываете
во внешнем ключе и родительском ключе, должны быть в состоянии справочной целостности. Иначе команда будет отклонена. Хотя ALTER TABLE очень
полезна из-за ее удобства, вы должны будете в вашей системе, по возможности каждый раз, сначала формировать структурные принципы, типа справочной целостности.
ПЕРВИЧНЫЙ КЛЮЧ КАК УНИКАЛЬНЫЙ ВНЕШНИЙ КЛЮЧ
Ссылка ваших внешних ключей только на первичные ключи, как мы это делали
в типовых таблицах, - хороша стратеги. Когда вы используете внешние клю-
чи, вы связываете их не просто с родительскими ключами на которые они ссылаются; вы связываете их с определенной строкой таблицы где этот родительс-
кий ключ будет найден. Сам по себе родительский ключ не обеспечивает ника-
кой информации которая бы не была уже представлена во внешнем ключе.
Смысл, например, пол snum как внешнего ключа в таблице Заказчиков - это
связь которую он обеспечивает, не к значению пол snum на которое он ссылается, а к другой информации в таблице Продавцов, такой например как,
имена продавцов, их местоположение, и так далее.
Внешний ключ - это не просто связь между двум идентичными значениями;
это - связь, с помощью этих двух значений, между двум строками таблицы
указанной в запросе.
Это поле snum может использоваться чтобы связывать любую информацию
в строке из таблицы Заказчиков со ссылочной строкой из таблицы Продавцов -
например чтобы узнать - живут ли они в том же самом городе, кто имеет более длинное имя, имеет ли продавец кроме данного заказчика каких-то других
заказчиков, и так далее.
Так как цель первичного ключа состоит в том, чтобы идентифицировать уникальность строки, это более логичный и менее неоднозначный выбор для внешнего ключа. Для любого внешнего ключа который использует уникальный ключ
как родительский ключ, вы должны создать внешний ключ который бы использовал первичный ключ той же самой таблицы для того же самого действия.
Внешний ключ который не имеет никакой другой цели кроме связывания строк,
напоминает первичный ключ используемый исключительно для идентификации
строк, и является хорошим средством сохранить структуру вашей базы данных
ясной и простой, и - следовательно создающей меньше трудностей.
ЧТО СЛУЧИТСЯ, ЕСЛИ ВЫ ВЫПОЛНИТЕ КОМАНДУ МОДИФИКАЦИИ
Давайте условимся, что все внешние ключи созданные в наших таблицах приме-
ров, объявлены и предписаны с ограничениями внешнего ключа, следующим
образом :
CREATE TABLE Salespeople
(snum integer NOT NULL PRIMARY KEY,
sname char(10) NOT NULL,
city char(10),
comm decimal);
CREATE TABLE Customers
(cnum integer NOT NULL PRIMARY KEY,
cname char(10) NOT NULL,
city char(10),
rating integer,
snum integer,
FOREIGN KEY (snum) REFERENCES Salespeople,
UNIQUE (cnum, snum) ;
CREATE TABLE Orders
(cnum integer NOT NULL PRIMARY KEY,
amt decimal,
odate date NOT NULL,
cnum integer NOT NULL
snum integer NOT NULL
FOREIGN KEY (cnum, snum) REFERENCES
CUSTOMERS (cnum, snum);
ВКЛЮЧЕНИЕ ОПИСАНИЙ ТАБЛИЦЫ
Имеется несколько атрибутов таких определений о которых нужно поговорить.
Причина по которой мы решили сделать пол cnum и snum в таблице Порядков, единым внешним ключом - это гарантия того, что для каждого заказчика
содержащегося в порядках, продавец кредитующий этот порядок - тот же что
и указанный в таблице Заказчиков. Чтобы создать такой внешний ключ, мы бы-
ли бы должны поместить ограничение таблицы UNIQUE в два пол таблицы
Заказчиков, даже если оно необязательно для самой этой таблицы. Пока поле
cnum в этой таблица имеет ограничение PRIMARY KEY, оно будет уникально
в любом случае, и следовательно невозможно получить еще одну комбинацию
пол cnum с каким-то другим полем.
Создание внешнего ключа таким способом поддерживает целостность базы
данных, даже если при этом вам будет запрещено внутреннее прерывание по
ошибке и кредитовать любого продавца, иного чем тот который назначен
именно этому заказчику.
С точки зрения поддержания целостности базы данных, внутренние прерывания ( или исключения) конечно же нежелательны. Если вы их допускаете и
в то же врем хотите поддерживать целостность вашей базы данных, вы можете объявить пол snum и cnum в таблице Порядков независимыми внешними ключами этих полей в таблице Продавцов и таблице Заказчиков, соответственно.
Фактически, использование пол snum в таблице Порядков, как мы это делали,
необязательно, хотя это полезно было сделать для разнообразия. Поле cnum
связывая каждый порядок заказчиков в таблице Заказчиков, в таблице Порядков
и в таблице Заказчиков, должно всегда быть общим чтобы находить правильное
поле snum для данного порядка ( не разрешая никаких исключений ).
Это означает что мы записываем фрагмент информации - какой заказчик назначен к какому продавцу - дважды, и нужно будет выполнять дополнительную
работу чтобы удостовериться, что обе версии согласуются.
Если мы не имеем ограничения внешнего ключа как сказано выше, эта ситуация будет особенно проблематична, потому что каждый порядок нужно будет
проверять вручную ( вместе с запросом ), чтобы удостовериться что именно
соответствующий продавец кредитовал каждую соответствующую продажу.
Наличие такого типа информационной избыточности в вашей базе данных, называется деморализация ( denormalization ), что нежелательно в идеальной
реляционной базе данных, хотя практически и может быть разрешена.
Деморализация может заставить некоторые запросы выполняться быстрее,
поскольку запрос в одной таблице выполняется всегда значительно быстрее
чем в объединении.
ДЕЙСТВИЕ ОГРАНИЧЕНИЙ
Как такие ограничения воздействуют на возможность и невозможность
Вами использовать команды модификации DML? Для полей, определен-
ных как внешние ключи, ответ довольно простой: любые значения которые вы
помещаете в эти пол с командой INSERT или UPDATE должны уже быть
представлены в их родительских ключах. Вы можете помещать пустые(NULL)
значения в эти пол, несмотря на то что значения NULL не позволительны в
родительских ключах, если они имеют ограничение NOT NULL. Вы можете
удалять (DELETE ) любые строки с внешними ключами не используя роди-
тельские ключи вообще.
Поскольку затронут вопрос об изменении значений родительского ключа,
ответ, по определению ANSI, еще проще, но возможно несколько более
ограничен: любое значение родительского ключа ссылаемого с помощью
значения внешнего ключа, не может быть удалено или изменено.
Это означает, например, что вы не можете удалить заказчика из таблицы
Заказчиков пока он еще имеет порядки в таблице Порядков.
В зависимости от того, как вы используете эти таблицы, это может быть
или желательно или хлопотно. Однако - это конечно лучше чем иметь сис-
тему, которая позволит вам удалить заказчика с текущими порядками и оставить таблицу Порядков ссылающейся на несуществующих заказчиков.
Смысл этой системы ограничения в том, что создатель таблицы Порядков,
используя таблицу Заказчиков и таблицу Продавцов как родительские клю-
чи может наложить значительные ограничения на действия в этих таблицах.
По этой причине, вы не сможете использовать таблицу которой вы не распоряжаетесь ( т.е. не вы ее создавали и не вы являетесь ее владельцем), по-
ка владелец(создатель) этой таблицы специально не передаст вам на это
право ( что объясняется в Главе 22).
Имеются некоторые другие возможные действия изменения родительс-
кого ключа, которые не являются частью ANSI, но могут быть найдены в
некоторых коммерческих программах. Если вы хотите изменить или удалить
текущее ссылочное значение родительского ключа, имеется по существу
три возможности:
* Вы можете ограничить, или запретить, изменение ( способом ANSI ),
обозначив, что изменения в родительском ключе - ограничены.
* Вы можете сделать изменение в родительском ключе и тем самым сде-
лать изменения во внешнем ключе автоматическим, что называется -
каскадным изменением.
* Вы можете сделать изменение в родительском ключе, и установить вне-
шний ключ в NULL, автоматически ( полагая, что NULLS разрешен во
внешнем ключе ), что называется - пустым изменением внешнего ключа.
Даже в пределах этих трех категорий, вы можете не захотеть обрабатывать все
команды модификации таким способом. INSERT, конечно, к делу не относится.
Он помещает новые значения родительского ключа в таблицу, так что ни одно
из этих значений не может быть вызвано в данный момент. Однако, вы можете
захотеть позволить модификациям быть каскадными, но без удалений, и наоборот.
Лучшей может быть ситуация которая позволит вам определять любую из трех
категорий, независимо от команд UPDATE и DELETE. Мы будем следовательно ссылаться на эффект модификации (update effects) и эффект удаления
( delete effects ), которые определяют, что случится если вы выполните коман-
ды UPDATE или DELETE в родительском ключе.
Эти эффекты, о которых мы говорили, называются:
Ограниченные (RESTRICTED) изменения,
Каскадируемые (CASCADES) изменения, и
Пустые (NULL) изменения.
Фактические возможности вашей системы должны быть в строгом стандар-
те ANSI - это эффекты модификации и удаления, оба, автоматически ограниченные - для более идеальной ситуации описанной выше. В качестве иллюстрации, мы покажем несколько примеров того, что вы можете делать с полным
набором эффектов модификации и удаления. Конечно, эффекты модификации
и удаления, являющиеся нестандартными средствами, испытывают недостаток
в стандартном госинтаксисе. Синтаксис который мы используем здесь, прост в
написании и будет служить в дальнейшем для иллюстрации функций этих
эффектов.
Для полноты эксперимента, позволим себе предположить что вы имеете причи-
ну изменить поле snum таблицы Продавцов в случае, когда наша таблица
Продавцов изменяет разделы. ( Обычно изменение первичных ключей это не то
что мы рекомендуем делать практически. Просто это еще один из доводов для
имеющихся первичных ключей которые не умеют делать ничего другого кроме
как, действовать как первичные ключи: они не должны изменяться. ) Когда вы
изменяете номер продавца, вы хотите чтобы были сохранены все его заказчики.
Однако, если этот продавец покидает свою фирму или компанию, вы можете не
захотеть удалить его заказчиков, при удалении его самого из базы данных.
Взамен, вы захотите убедиться, что заказчики назначены кому-нибудь еще.
Чтобы сделать это вы должны указать UPDATE с Каскадируемым эффектом ,
и DELETE с Ограниченным эффектом.
CREATE TABLE Customers
(cnum integer NOT NULL PRIMARY KEY,
cname char(10) NOT NULL,
city char(10),
rating integer,
snum integer REFERENCES Salespeople,
UPDATE OF Salespeople CASCADES,
DELETE OF Salespeople RESTRICTED);
Если вы теперь попробуете удалить Peel из таблицы Продавцов, команда
будет не допустима, пока вы не измените значение пол snum заказчиков
Hoffman и Clemens для другого назначенного продавца.
С другой стороны, вы можете изменить значение пол snum для Peel на
1009, и Hoffman и Clemens будут также автоматически изменены.
Третий эффект - Пустые (NULL) изменения. Бывает, что когда продавцы
оставляют компанию, их текущие порядки не передаются другому продавцу.
С другой стороны, вы хотите отменить все порядки автоматически для заказ-
чиков, чьи счета вы удалите. Изменив номера продавца или заказчика можно
просто передать их ему. Пример ниже показывает, как вы можете создать
таблицу Порядков с использованием этих эффектов.
CREATE TABLE Orders
(onum integer NOT NULL PRIMARY KEY,
amt decimal,
odate date NOT NULL
cnum integer NOT NULL REFERENCES Customers
snum integer REFERENCES Salespeople,
UPDATE OF Customers CASCADES,
DELETE OF Customers CASCADES,
UPDATE OF Salespeople CASCADES,
DELETE OF Salespeople NULLS);
Конечно, в команде DELETE с эффектом Пустого изменения в таблице
Продавцов, ограничение NOT NULL должно быть удалено из пол snum.
ВНЕШНИЕ КЛЮЧИ КОТОРЫЕ ССЫЛАЮТСЯ ОБРАТНО К ИХ ПОДЧИНЕННЫМ ТАБЛИЦАМ
Как было упомянуто ранее, ограничение FOREIGN KEY может представить
им этой частной таблице, как таблицы родительского ключа. Далеко не бу-
дучи простой, эта особенность может пригодиться. Предположим, что мы
имеем таблицу Employees с полем manager(администратор). Это поле содер-
жит номера каждого из служащих, некоторые из которых являются еще и ад-
министраторами.
Но так как каждый администратор - в то же врем остается служащим, то
он естественно будут также представлен в этой таблице. Давайте создадим
таблицу, где номер служащего ( столбец с именем empno ), объявляется как
первичный ключ, а администратор, как внешний ключ, будет ссылаться
на нее:
CREATE TABLE Employees
(empno integer NOT NULL PRIMARY KEY,
name char(10) NOT NULL UNIOUE,
manager integer REFERENCES Employees);
( Так как внешний ключ это ссылаемый первичный ключ таблицы, список
столбцов может быть исключен. ) Имеется содержание этой таблицы:
EMPNO NAME MANAGER
_____ ________ _______
1003 Terrence 2007
2007 Atali NULL
1688 McKenna 1003
2002 Collier 2007
Как вы можете видеть, каждый из них( но не Atali ) , ссылается на другого
служащего в таблице как на своего администратора. Atali, имеющий наивысший номер в таблице, должен иметь значение установленное в NULL.
Это дает другой принцип справочной целостности. Внешний ключ, который
ссылается обратно к частной таблице, должен позволять значения = NULL.
Если это не так, как бы вы могли вставить первую строку ?
Даже если эта первая строка ссылается к себе самой, значение родительского
ключа должно уже быть установлено, когда вводится значение внешнего клю-
ча. Этот принцип будет верен, даже если внешний ключ ссылается обратно к
частной таблице не напрямую а с помощью ссылки к другой таблице, которая
затем ссылается обратно к таблице внешнего ключа. Например, предположим,
что наша таблица Продавцов имеет дополнительное поле которое ссылается на
таблицу Заказчиков, так, что каждая таблица ссылается на другую, как показано
в следующем операторе CREATE TABLE:
CREATE TABLE Salespeople
(snum integer NOT NULL PRIMARY KEY,
sname char(10) NOT NULL,
city char(10),
comm declmal,
cnum integer REFERENCES Customers);
CREATE TABLE Customers
(cnum integer NOT NULL PRIMARY KEY,
cname char(10) NOT NULL,
city char(10),
rating integer,
snum integer REFERENCES Salespeople);
Это называется - перекрестной ссылкой.
SQL поддерживает это теоретически, но практически это может составить
проблему. Люба таблица из этих двух, созданная первой является ссылоч-
ной таблицей которая еще не существует для другой. В интересах обеспечения перекрестной ссылки, SQL фактически позволяет это, но никакая таблица не будет пригодна для использования пока они обе находятся в процессе
создания. С другой стороны, если эти две таблицы создаются различными
пользователями, проблема становится еще более трудной.
Перекрестна ссылка может стать полезным инструментом, но она не без
неоднозначности и опасностей. Предшествующий пример, например, не сов-
сем пригоден для использования: потому что он ограничивает продавца оди-
ночным заказчиком, и кроме того совсем необязательно использовать перекрестную ссылку чтобы достичь этого. Мы рекомендуем чтобы вы были осторожны в его использовании и анализировали, как ваши программы управ-
лют эффектами модификации и удаления а также процессами привилегий
и диалоговой обработки запросов перед тем как вы создаете перекрестную
систему справочной целостности.
( Привилегии и диалоговая обработка запросов будут обсуждаться, соответственно, в Главах 22 И 23.)
РЕЗЮМЕ
Теперь вы имеете достаточно хороше управление справочной целостностью.
Основная идея в том, что все значения внешнего ключа ссылаются к указан-
ной строке родительского ключа. Это означает, что каждое значение внешне-
го ключа должно быть представлено один раз, и только один раз, в родитель-
ском ключе. Всякий раз, когда значение помещается во внешний ключ, роди-
тельский ключ проверяется, чтобы удостовериться, что его значение представлено; иначе, команда будет отклонена. Родительский ключ должен иметь
Первичный Ключ (PRIMARY KEY) или Уникальное (UNIQUE) ограничение, гарантирующее, что значение не будет представлено более чем один раз.
Попытка изменить значение родительского ключа, которое в настоящее врем
представлено во внешнем ключе, будет вообще отклонена.
Ваша система может, однако, предложить вам выбор, чтобы получить значение
внешнего ключа установленного в NULL или для получения нового значения ро-
дителького ключа, и указания какой из них может быть получен независимо
для команд UPDATE и DELETE.
Этим завершается наше обсуждение команды CREATE TABLE.
Далее мы представим вас другому типу команды - CREATE. В Главе 20,
вы обучитесь представлению объектов данных которые выглядят и действуют
подобно таблице, но в действительности являются результатами запросов.
Некоторые функции ограничений могут также выполняться представлениями,
так что вы сможете лучше оценить вашу потребность к ограничениям, после
того, как вы прочитаете следующие три главы.