19. ПОДДЕРЖКА ЦЕЛОСТНОСТИ ВАШИХ ДАННЫХ
РАНЕЕ В ЭТОЙ КНИГЕ, МЫ УКАЗЫВАЛИ НА ОПРЕДЕЛЕННЫЕ связи которые су-
ществуют между некоторыми полями наших типовых таблиц. Поле snum таб-
лицы Заказчиков, например, соответствует полю snum в таблице Продавцов
и таблице Порядков. Поле cnum таблицы Заказчиков также соответствует
полю cnum таблицы Порядков. Мы назвали этот тип связи - справочной це-
лостностью; и в ходе обсуждения, вы видели как ее можно использовать.
В этой главе, вы будете исследовать справочную целостность более
подробно и выясним все относительно ограничений которые вы можете ис-
пользовать чтобы ее поддерживать. Вы также увидете, как предписывается
это ограничение когда вы используете команды модификации DML. Посколь-
ку справочная целостность включает в себя связь полей или групп полей,
часто в разных таблицах, это действие может быть несколько сложнее чем
другие ограничения. По этой причине, хорошо иметь с ней полное зна-
комство, даже если вы не планируете создавать таблицы. Ваши команды
модификации могут стать эффективнее с помощью ограничения справочной
целостности ( как и с помощью других ограничений, но ограничение спра-
вочной целостности может воздействовать на другие таблицы кроме тех в
которых оно определено), а определенные функции запроса, такие как
обьединения, являются многократно структурированы в терминах связей
справочной целостности ( как подчеркивалось в Главе 8 ).
= ВНЕШНИЙ КЛЮЧ И РОДИТЕЛЬСКИЙ КЛЮЧ =
Когда все значения в одном поле таблицы представлены в поле другой
таблицы, мы говорим что первое поле ссылается на второе. Это указывает
на прямую связь между значениями двух полей. Например, каждый из за-
казчиков в таблице Заказчиков имеет поле snum которое указывает на
продавца назначенного в таблице Продавцов. Для каждого порядка в таб-
лице Порядков, имеется один и только этот продавец и один и только
этот заказчик. Это отображается с помощью полей snum и cnum в таблице
Порядков.
Когда одно поле в таблице ссылается на другое, оно называется -
внешним ключом; а поле на которое оно ссылается, называется - роди-
тельским ключом. Так что поле snum таблицы Заказчиков - это внешний
ключ, а поле snum на которое оно ссылается в таблице Продавцов - это
родительский ключ.
Аналогично, поля cnum и snum таблицы Порядков - это внешние ключи
которые ссылаются к их родительским ключам с именами в таблице Заказ-
чиков и таблице Продавцов. Имена внешнего ключа и родительского ключа
не обязательно должны быть одинаковыми, это - только соглашение кото-
рому мы следуем чтобы делать соединение более понятным.
МНОГО-СТОЛБЦОВЫЕ ВНЕШНИЕ КЛЮЧИ
В действительности, внешний ключ не обязательно состоит только из
одного поля. Подобно первичному ключу, внешний ключ может иметь любое
число полей, которые все обрабатываются как единый модуль. Внешний
ключ и родительский ключ на который он ссылается, конечно же, должны
иметь одинаковый номер и тип поля, и находиться в одинаковом порядке.
Внешние ключи состоящие из одного поля - те что мы использовали исклю-
чительно в наших типовых таблицах, наиболее общие. Чтобы сохранить
простоту нашего обсуждения, мы будем часто говорить о внешнем ключе
как об одиночном столбце. Это не случайно. Если это не отметить, любой
скажет о поле которое является внешним ключом, что оно также относится
и к группе полей которая является внешним ключом.
СМЫСЛ ВНЕШНЕГО И РОДИТЕЛЬСКОГО КЛЮЧЕЙ
Когда поле - является внешним ключом, оно определеным образом связа-
но с таблицей на которую он ссылается. Вы, фактически, говорите - "
каждое значение в этом поле ( внешнем ключе ) непосредственно привяза-
но к значению в другом поле ( родительском ключе )." Каждое значение (
каждая строка ) внешнего ключа должно недвусмысленно ссылаться к одно-
му и только этому значению (строке) родительского ключа. Если это так,
то фактически ваша система, как говорится, будет в состоянии справоч-
ной целостности.
Вы можете увидеть это на примере. Внешний ключ snum в таблице Заказ-
чиков имеет значение 1001 для строк Hoffman и Clemens. Предположим что
мы имели две строки в таблице Продавцов со значением в поле snum =
1001. Как мы узнаем, к которому из двух продавцов были назначены за-
казчики Hoffman и Clemens ? Аналогично, если нет никаких таких строк в
таблице Продавцов, мы получим Hoffman и Clemens назначенными к продав-
цу которого не существует!
Понятно, что каждое значение во внешнем ключе должно быть представ-
лено один, и только один раз, в родительском ключе.
Фактически, данное значение внешнего ключа может ссылаться только к
одному значению родительского ключа не предполагая обратной возможнос-
ти: т.е. любое число внешних ключей может ссылать к единственному зна-
чению родительского ключа. Вы можете увидеть это в типовых таблицах
наших примеров. И Hoffman и Clemens назначены к Peel, так что оба их
значения внешнего ключа совпадают с одним и тем же родительским клю-
чом, что очень хорошо. Значение внешнего ключа должно ссылаться только
к одному значению родительского ключа, зато значение родительского
ключа может ссылаться с помощью любого колличества значений внешнего
ключа. В качестве иллюстрации, значения внешнего ключа из таблицы За-
казчиков, совпавшие с их родительским ключом в Продавцов таблице, по-
казываются в Рисунке 19.1. Для удобства мы не учитывали поля не отно-
сящиеся к этому примеру.
======= ОГРАНИЧЕНИЕ FOREIGN KEY =======
SQL поддерживает справочную целостность с ограничением FOREIGN KEY.
Хотя ограничение FOREIGN KEY - это новая особенность в SQL, оно еще не
обеспечивает его универсальности. Кроме того, некоторые его реализа-
ции, более сложны чем другие. Эта функция должна ограничивать значения
которые вы можете ввести в вашу базу данных чтобы заставить внешний
ключ и родительский ключ соответствовать принципу справочной целост-
ности. Одно из действий ограничения Внешнего Ключа - это отбрасывание
значений для полей ограниченных как внешний ключ который еще не предс-
тавлен в родительском ключе. Это ограничение также воздействует на ва-
шу способность изменять или удалять значения родительского ключа ( мы
будем обсуждать это позже в этой главе ).
КАК МОЖНО ПОЛЯ ПРЕДСТАВИТЬ В КАЧЕСТВЕ ВНЕШНИХ КЛЮЧЕЙ
Вы используете ограничение FOREIGN KEY в команде CREATE TABLE ( или
ALTER TABLE ), которая содержит поле которое вы хотите обьявить внеш-
ним ключом. Вы даете имя родительскому ключу на которое вы будете ссы-
латься внутри ограничения FOREIGN KEY. Помещение этого ограничения в
команду - такое же что в для других ограничений обсужденных в предыду-
щей главе.
{****************************************************}
Рисунок 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 TAB-
LE, для применения ограничения FOREIGN KEY, значения которые Вы указы-
ваете во внешнем ключе и родительском ключе, должны быть в состоянии
справочной целостности. Иначе команда будет отклонена. Хотя ALTER TAB-
LE очень полезна из-за ее удобства, вы должны будете в вашей системе,
по возможности каждый раз, сначала формировать структурные принципы,
типа справочной целостности.
ВНЕШНИЙ КЛЮЧ КАК ОГРАНИЧЕНИЕ СТОЛБЦОВ
Вариант ограничения столбца ограничением FOREIGN KEY - по другому
называется - ссылочное ограничение (REFERENCES), так как он фактически
не содержит в себе слов FOREIGN KEY, а просто использует слово REFE-
RENCES, и далее имя родительского ключа, подобно этому:
CREATE TABLE Customers
( cnum integer NOT NULL PRIMARY KEY,
cname char(10),
city char(10),
snum integer REFERENCES Salespeople (snum));
Вышеупомянутое определяет Customers.snum как внешний ключ у которого
родительский ключ - это Salespeople.snum. Это эквивалентно такому ог-
раничению таблицы:
FOREIGN KEY (snum) REGERENCES Salespeople (snum)
НЕ УКАЗЫВАТЬ СПИСОК СТОЛБЦОВ ПЕРВИЧНЫХ КЛЮЧЕЙ
Используя ограничение FOREIGN KEY таблицы или столбца, вы можете не
указывать список столбцов родительского ключа если родительский ключ
имеет ограничение PRIMARY KEY. Естественно, в случае ключей со многими
полями, порядок столбцов во внешних и первичных ключах должен совпа-
дать, и, в любом случае, принцип совместимости между двумя ключами все
еще применим. Например, если мы поместили ограничение PRIMARY KEY в
поле snum таблицы Продавцов, мы могли бы использовать его как внешний
ключ в таблице Заказчиков (подобно предыдущему примеру) в этой коман-
де:
CREATE TABLE Customers
( cnum integer NOT NULL PRIMARY KEY,
cname char(10),
city char(10),
snum integer REFERENCES Salespeople);
Это средство встраивалось в язык, чтобы поощрять вас использовать
первичные ключи в качестве родительских ключей.
КАК СПРАВОЧНАЯ ЦЕЛОСТНОСТЬ ОГРАНИЧИВАЕТ
ЗНАЧЕНИЯ РОДИТЕЛЬСКОГО КЛЮЧА
Поддержание справочной целостности требует некоторых ограничений на
значения, которые могут быть представлены в полях, обьявленных как
внешний ключ и родительский ключ. Родительский ключ должен быть струк-
турен, чтобы гарантировать, что каждое значение внешнего ключа будет
соответствовать одной указанной строке. Это означает, что он (ключ)
должен быть уникальным и не содержать никаких пустых значений(NULL).
Этого не достаточно для родительского ключа в случае выполнения такого
требования как при объявлении внешнего ключа. SQL должен быть уверен
что двойные значения или пустые значения (NULL) не были введены в ро-
дительский ключ. Следовательно вы должны убедиться, что все поля, ко-
торые используются как родительские ключи, имеют или ограничение PRI-
MARY KEY или ограничение UNIQUE, наподобии ограничения NOT NULL.
ПЕРВИЧНЫЙ КЛЮЧ КАК УНИКАЛЬНЫЙ ВНЕШНИЙ КЛЮЧ
Ссылка ваших внешних ключей только на первичные ключи, как мы это
делали в типовых таблицах, - хорошая стратегия. Когда вы используете
внешние ключи, вы связываете их не просто с родительскими ключами на
которые они ссылаются; вы связываете их с определенной строкой таблицы
где этот родительский ключ будет найден. Сам по себе родительский ключ
не обеспечивает никакой информации которая бы не была уже представлена
во внешнем ключе. Смысл, например, поля snum как внешнего ключа в таб-
лице Заказчиков - это связь которую он обеспечивает, не к значению по-
ля snum на которое он ссылается, а к другой информации в таблице Про-
давцов, такой например как, имена продавцов, их местоположение, и так
далее. Внешний ключ - это не просто связь между двумя идентичными зна-
чениями; это - связь, с помощью этих двух значений, между двумя стро-
ками таблицы указанной в запросе.
Это поле snum может использоваться чтобы связывать любую информацию
в строке из таблицы Заказчиков со ссылочной строкой из таблицы Продав-
цов - например чтобы узнать - живут ли они в том же самом городе, кто
имеет более длинное имя, имеет ли продавец кроме данного заказчика ка-
ких-то других заказчиков, и так далее.
Так как цель первичного ключа состоит в том, чтобы идентифицировать
уникальность строки, это более логичный и менее неоднозначный выбор
для внешнего ключа. Для любого внешнего ключа который использует уни-
кальный ключ как родительский ключ, вы должны создать внешний ключ ко-
торый бы использовал первичный ключ той же самой таблицы для того же
самого действия. Внешний ключ который не имеет никакой другой цели
кроме связывания строк, напоминает первичный ключ используемый исклю-
чительно для идентификации строк, и является хорошим средством сохра-
нить структуру вашей базы данных ясной и простой, и - следовательно
создающей меньше трудностей.
ОГРАНИЧЕНИЯ ВНЕШНЕГО КЛЮЧА
Внешний ключ, в частности, может содержать только те значения кото-
рые фактически представлены в родительском ключе или пустые(NULL). По-
пытка ввести другие значения в этот ключ будет отклонена.
Вы можете обьявить внешний ключ как NOT NULL, но это необязательно,
и в большинстве случаев, нежелательно. Например, предположим, что вы
вводите заказчика не зная заранее, к какому продавцу он будет назна-
чен. Лучший выход в этой ситуации, будет если использовать значение
NOT NULL, которое должно быть изменено позже на конкретное значение.
ЧТО СЛУЧИТСЯ, ЕСЛИ ВЫ ВЫПОЛНИТЕ КОМАНДУ МОДИФИКАЦИИ
Давайте условимся, что все внешние ключи созданые в наших таблицах
примеров, обьявлены и предписаны с ограничениями внешнего ключа, сле-
дующим образом:
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, вы обучитесь представлению объектов дан-
ных которые выглядят и действуют подобно таблице, но в действительнос-
ти являются результатами запросов. Некоторые функции ограничений могут
также выполняться представлениями, так что вы сможете лучше оценить
вашу потребность к ограничениям, после того, как вы прочитаете следую-
щие три главы.
*************** РАБОТА С SQL *************
1. Создайте таблицу с именем Cityorders. Она должна содержать такие же
поля onum, amt, и snum что и таблица Порядков, и такие же поля cnum
и city что и таблица Заказчиков, так что порядок каждого заказчика
будет вводиться в эту таблицу вместе с его городом. Поле оnum будет
первичным ключом Cityorders. Все поля в Cityorders должны иметь ог-
раничения при сравнении с таблицами Заказчиков и Порядков. Допуска-
ется, что родительские ключи в этих таблицах уже имеют соответству-
ющие ограничения.
2. Усложним проблему. Переопределите таблицу Порядков следующим обра-
зом: добавьте новый столбец с именем prev, который будет идентифи-
цирован для каждого порядка, поле onum предыдущего порядка для это-
го текущего заказчика. Выполните это с использованием внешнего клю-
ча ссылающегося на саму таблицу Порядков. Внешний ключ должен ссы-
латься также на поле cnum заказчика, обеспечивающего определенную
предписанную связь между текущим порядком и ссылаемым.
( См. Приложение A для ответов. )
|
