19. ПОДДЕРЖКА ЦЕЛОСТНОСТИ ВАШИХ ДАННЫХ

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

В этой главе, вы будете исследовать справочную целостность более под- робно и выясним все относительно ограничений которые вы можете использовать чтобы ее поддерживать. Вы также увидите, как предписывает- с это ограничение когда вы используете команды модификации DML. Поскольку справочна целостность включает в себя связь полей или групп полей, часто в разных таблицах, это действие может быть несколько слож- нее чем другие ограничения. По этой причине, хорошо иметь с ней полное знакомство, даже если вы не планируете создавать таблицы. Ваши команды модификации могут стать эффективнее с помощью ограничения справочной целостности ( как и с помощью других ограничений, но ограничение справочной целостности может воздействовать на другие таблицы кроме тех в которых оно определено), а определенные функции запроса, такие как объединения, являются многократно структурированы в терминах связей справочной целостности ( как подчеркивалось в Главе 8 ).

ВНЕШНИЙ КЛЮЧ И РОДИТЕЛЬСКИЙ КЛЮЧ

МНОГО-СТОЛБЦОВЫЕ ВНЕШНИЕ КЛЮЧИ

СМЫСЛ ВНЕШНЕГО И РОДИТЕЛЬСКОГО КЛЮЧЕЙ

ОГРАНИЧЕНИЕ FOREIGN KEY

КАК МОЖНО ПОЛЯ ПРЕДСТАВИТЬ В КАЧЕСТВЕ ВНЕШНИХ КЛЮЧЕЙ

ВНЕШНИЙ КЛЮЧ КАК ОГРАНИЧЕНИЕ ТАБЛИЦЫ

 
 FOREIGN KEY <COLUMN list> REFERENCES 
 <PKTABLE> [ <COLUMN list> ] 

 
 
 CREATE TABLE Customers 
 ( cnum integer NOT NULL PRIMARY KEY 
 cname char(10), 
 city char(10), 
 snum integer, 
 FOREIGN KEY (snum) REFERENCES Salespeople 
 ( snum ); 

ВНЕШНИЙ КЛЮЧ КАК ОГРАНИЧЕНИЕ СТОЛБЦОВ

 
 
 CREATE TABLE Customers 
 ( cnum integer NOT NULL PRIMARY KEY, 
 cname char(10), 
 city char(10), 
 snum integer REFERENCES Salespeople (snum)); 

НЕ УКАЗЫВАТЬ СПИСОК СТОЛБЦОВ ПЕРВИЧНЫХ КЛЮЧЕЙM

 
 CREATE TABLE Customers 
 ( cnum integer NOT NULL PRIMARY KEY, 
 cname char(10), 
 city char(10), 
 snum integer REFERENCES Salespeople); 
 

КАК СПРАВОЧНАЯ ЦЕЛОСТНОСТЬ ОГРАНИЧИВАЕТ ЗНАЧЕНИЯ РОДИТЕЛЬСКОГО КЛЮЧА

ПЕРВИЧНЫЙ КЛЮЧ КАК УНИКАЛЬНЫЙ ВНЕШНИЙ КЛЮЧ

ОГРАНИЧЕНИЯ ВНЕШНЕГО КЛЮЧА

ЧТО СЛУЧИТСЯ, ЕСЛИ ВЫ ВЫПОЛНИТЕ КОМАНДУ МОДИФИКАЦИИ

 
 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); 

ВКЛЮЧЕНИЕ ОПИСАНИЙ ТАБЛИЦЫ

ДЕЙСТВИЕ ОГРАНИЧЕНИЙ

• Вы можете ограничить, или запретить, изменение ( способом 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 для ответов. )