SQLГлава 19. Поддержка целостности ваших данных
Ранее мы указывали на определённые связи, которые существуют между некоторыми полями типовых таблиц. Поле snum таблицы Заказчиков, например, соответствует полю snum в таблице Продавцов и таблице Заказов. Поле В этой главе мы будем исследовать ссылочную целостность более подробно и выясним всё относительно ограничений, которые вы можете использовать, чтобы её поддерживать. Вы также увидите, как предписывается это ограничение, когда вы используете команды модификации DML. Поскольку ссылочная целостность включает в себя связь полей или групп полей, часто в разных таблицах, это действие может быть несколько сложнее, чем другие ограничения. По этой причине хорошо иметь с ней полное знакомство, даже если вы не планируете создавать таблицы. Ваши команды модификации могут стать эффективнее с помощью ограничения ссылочной целостности (как и с помощью других ограничений, но ограничение ссылочной целостности может воздействовать на другие таблицы помимо тех, в которых оно определено), а определённые функции запроса, такие как объединения, являются многократно структурированными, в терминах связей ссылочной целостности (как подчеркивалось в Главе 8). Внешний ключ и родительский ключ
Когда все значения в поле одной таблицы представлены в поле другой таблицы, мы говорим, что первое поле ссылается на второе. Это указывает на прямую связь между значениями двух полей. Например, каждый из заказчиков в таблице Заказчиков имеет поле
Когда одно поле в таблице ссылается на другое, оно называется внешним ключом, а поле, на которое оно ссылается, называется родительским ключом. Так что поле
Аналогично, поля Многостолбцовые внешние ключиВ реальности внешний ключ не обязательно состоит только из одного поля. Подобно первичному ключу, внешний ключ может иметь любое число полей, которые все обрабатываются как единый модуль. Внешний ключ и родительский ключ, на который он ссылается, конечно же, должны иметь одинаковый номер и тип поля и находиться в одинаковом заказе. Внешние ключи, состоящие из одного поля, - те, что мы использовали в наших типовых таблицах, - наиболее распространённые. Чтобы сохранить простоту нашего обсуждения, мы будем часто говорить о внешнем ключе как об одиночном столбце. Это не случайно. Если это не отметить, любой скажет о поле, которое является внешним ключом, что это также относится и к группе полей, которые являются внешними ключами. Смысл внешнего и родительского ключейКогда поле является внешним ключом, оно определённым образом связано с таблицей, на которую оно ссылается. Вы фактически говорите: "каждое значение в этом поле (внешнем ключе) непосредственно привязано к значению в другом поле (родительском ключе)". Каждое значение (каждая строка) внешнего ключа должно недвусмысленно ссылаться на одно, и только это, значение (строку) родительского ключа. Если это так, то ваша система, как говорится, будет в состоянии ссылочной целостности.
Вы можете увидеть это на примере. Внешний ключ
Предположим, что мы имели две строки в таблице Продавцов со значением в поле Понятно, что каждое значение во внешнем ключе должно быть представлено один, и только один, раз в родительском ключе.
Фактически данное значение внешнего ключа может ссылаться только к одному значению родительского ключа, не предполагая обратной возможности: т.е. любое число внешних ключей может ссылаться на единственное значение родительского ключа. Вы можете увидеть это в типовых таблицах наших примеров. И Ограничение внешнего ключа (FOREIGN KEY)
SQL поддерживает ссылочную целостность с ограничением Одно из действий ограничения Внешнего Ключа - отбрасывание значений для полей, ограниченных как внешний ключ, который ещё не представлен в родительском ключе. Это ограничение также воздействует на вашу способность изменять или удалять значения родительского ключа (мы будем обсуждать это позже в этой главе). Как можно представить поля в качестве внешних ключей
Вы используете ограничение Подобно большинству ограничений, оно может быть ограничением таблицы или столбца, в форме таблицы, позволяющей использовать многочисленные поля как один внешний ключ. Внешний ключ как ограничение таблицыСинтаксис ограничения таблицы FOREIGN KEY: FOREIGN KEY <column_list> REFERENCES <pktable> [<column_list>]
Первый список столбцов это список из одного или более столбцов таблицы, которые разделены запятыми и будут созданы или изменены этой командой. Списки двух столбцов должны быть совместимы, т.е.:
Создадим таблицу Заказчиков с полем snum, определённым в качестве внешнего ключа, ссылающегося на таблицу Продавцов: 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));
Поле FOREIGN KEY (snum) REFERENCES 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); Это средство встраивалось в язык, чтобы поощрять вас использовать первичные ключи в качестве родительских ключей. Как ссылочная целостность ограничивает значения родительского ключа?
Поддержание ссылочной целостности требует некоторых ограничений на значения, которые могут быть представлены в полях, объявленных как внешний ключ и родительский ключ. Родительский ключ должен быть структурирован, чтобы гарантировать, что каждое значение внешнего ключа будет соответствовать одной указанной строке. Это означает, что он (ключ) должен быть уникальным и не должен содержать никаких пустых значений ( Первичный ключ как уникальный внешний ключ
Ссылка ваших внешних ключей только на первичные ключи, как мы это делали в типовых таблицах, - хорошая стратегия. Когда вы используете внешние ключи, вы связываете их не просто с родительскими ключами, на которые они ссылаются; вы связываете их с определённой строкой таблицы, где этот родительский ключ будет найден. Сам по себе родительский ключ не обеспечивает никакой информации, которая не была бы уже представлена во внешнем ключе. Смысл, например, поля
Это поле Так как цель первичного ключа состоит в том, чтобы идентифицировать уникальность строки, это более логичный и менее неоднозначный выбор для внешнего ключа. Для любого внешнего ключа, который использует уникальный ключ как родительский ключ, вы должны создать внешний ключ, который использовал бы первичный ключ той же самой таблицы для того же самого действия. Внешний ключ, который не имеет никакой другой цели, кроме связывания строк, напоминает первичный ключ, используемый исключительно для идентификации строк, и является хорошим средством сохранить структуру вашей БД ясной и простой и, следовательно, создающей меньше трудностей. Ограничения внешнего ключа
Внешний ключ, в частности, может содержать только те значения, которые фактически представлены в родительском ключе, или пустые (
Вы можете объявить внешний ключ как Что случится, если вы выполните команду модификации?Давайте условимся, что все внешние ключи созданные в наших таблицах примеров, объявлены и предписаны с ограничениями внешнего ключа следующим образом: 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)); Описание ограничений таблицы
Имеется несколько атрибутов таких определений, о которых нужно поговорить. Причина, по которой мы решили сделать поля
Чтобы создать такой внешний ключ, мы должны были бы поместить ограничение таблицы Создание внешнего ключа таким способом поддерживает целостность БД (даже если при этом вам будет запрещено внутреннее прерывание по ошибке) и кредитование любого продавца, отличного от того, который назначен именно этому заказчику. С точки зрения поддержания целостности БД, внутренние прерывания (или исключения), конечно же, нежелательны. Если вы их допускаете и, в то же время, хотите поддерживать целостность вашей БД, вы можете объявить поля snum и cnum в таблице Заказов независимыми внешними ключами этих полей в таблице Продавцов и таблице Заказчиков, соответственно.
Фактически не обязательно использовать поля
Если мы не имеем ограничения внешнего ключа, как сказано выше, эта ситуация будет особенно проблематична, потому что каждый порядок нужно будет проверять вручную (вместе с запросом), чтобы удостовериться, что именно соответствующий продавец кредитовал каждую соответствующую продажу. Наличие такого типа информационной избыточности в вашей БД называется денормализация ( Действие ограничений
Как ограничения воздействуют на возможность и невозможность использования команды модификации DML? Для полей, определённых как внешние ключи, ответ довольно простой: любые значения, которые вы помещаете в эти поля командой Поскольку затронут вопрос об изменении значений родительского ключа, ответ, по определению ANSI, ещё проще, но, возможно, несколько более ограничен: любое значение родительского ключа, на который ссылаются с помощью значения внешнего ключа, не может быть удалено или изменено. Это означает, например, что вы не можете удалить заказчика из таблицы Заказчиков, пока он ещё имеет заказы в таблице Заказов. В зависимости от того, как вы используете эти таблицы, это может быть или желательно, или хлопотно. Однако это, конечно, лучше, чем иметь систему, которая позволит вам удалить заказчика с текущими заказами и оставить таблицу Заказов ссылающейся на несуществующих заказчиков. Смысл этой системы ограничения в том, что создатель таблицы Заказов, используя таблицу Заказчиков и таблицу Продавцов как родительские ключи, может наложить значительные ограничения на действия в этих таблицах. По этой причине вы не сможете использовать таблицу которой вы не распоряжаетесь (т.е. не вы её создавали и не вы являетесь её владельцем), пока владелец (создатель) этой таблицы специально не передаст вам на это право (что объясняется в Главе 22). Имеются некоторые другие возможные действия изменения родительского ключа, которые не являются частью ANSI, но могут быть найдены в некоторых коммерческих программах. Если вы хотите изменить или удалить текущее ссылочное значение родительского ключа, имеются три возможности:
Даже в пределах этих трёх категорий вы можете не захотеть обрабатывать все команды модификации таким способом.
Лучшей может быть ситуация, которая позволит вам определять любую из трёх категорий, независимо от команд UPDATE и DELETE. Мы будем, следовательно, ссылаться на эффекты модификации ( Фактические возможности вашей системы должны строго соответствовать стандарту ANSI - это эффекты модификации и удаления, оба автоматически ограниченные - для более идеальной ситуации, описанной выше. В качестве иллюстрации мы покажем несколько примеров того, что вы можете делать с полным набором эффектов модификации и удаления. Конечно, эффекты модификации и удаления, являющиеся нестандартными средствами, испытывают недостаток в стандартном синтаксисе. Синтаксис, который мы используем здесь, прост в написании и будет служить в дальнейшем для иллюстрации функций этих эффектов. Для полноты эксперимента позволим себе предположить, что вы имеете причину изменить поле snum таблицы Продавцов в случае, когда наша таблица Продавцов изменяет разделы. (Обычно изменение первичных ключей это не то, что мы рекомендуем делать практически. Просто это ещё один из доводов, чтобы иметь первичные ключи, которые не умеют делать ничего другого, кроме как действовать как первичные ключи: они не должны изменяться.)
Когда вы изменяете номер продавца, вы хотите, чтобы были сохранены все его заказчики. Однако, если этот продавец покидает свою фирму или компанию, вы можете не захотеть удалить его заказчиков при удалении его самого из БД. Взамен вы захотите убедиться, что заказчики назначены кому-нибудь ещё. Чтобы сделать это, вы должны указать 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);
Если вы теперь попробуете удалить
Третий эффект - Пустые ( 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 NULL); Конечно, в команде DELETE с эффектом Пустого изменения в таблице Продавцов, ограничение NOT NULL должно быть удалено из поля snum. Внешние ключи, которые ссылаются на свои подчиненные таблицы
Как было упомянуто ранее, ограничение
Предположим, что мы имеем таблицу
Давайте создадим таблицу, где номер служащего (столбец с именем 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
Как вы видите, каждый из них (но не
Например, предположим, что наша таблица Продавцов имеет дополнительное поле, которое ссылается на таблицу Заказчиков так, что каждая таблица ссылается на другую, как показано в следующем операторе CREATE TABLE Salespeople (snum integer NOT NULL PRIMARY KEY, sname char(10) NOT NULL, city char(10), comm decimal, 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.) РезюмеТеперь вы имеете достаточно хорошее управление ссылочной целостностью. Основная идея в том, что все значения внешнего ключа ссылаются на указанную строку родительского ключа. Это означает, что каждое значение внешнего ключа должно быть представлено один раз, и только один раз, в родительском ключе. Всякий раз, когда значение помещается во внешний ключ, родительский ключ проверяется, чтобы удостовериться, что его значение представлено; иначе команда будет отклонена.
Родительский ключ должен иметь Первичный Ключ (
Этим завершается наше обсуждение команды Работа с SQL1. Создайте таблицу с именем Cityorders. Она должна содержать такие же поля onum, amt и snum, что и таблица Заказов, и такие же поля cnum и city, что и таблица Заказчиков, так что заказ каждого заказчика будет вводиться в эту таблицу вместе с его городом. Поле оnum будет первичным ключом Cityorders. Все поля в Cityorders должны иметь ограничения при сравнении с таблицами Заказчиков и Заказов. Допускается, что родительские ключи в этих таблицах уже имеют соответствующие ограничения. 2. Усложним проблему. Переопределите таблицу Заказов следующим образом: добавьте новый столбец с именем prev, который будет идентифицирован для каждого заказа, поле onum предыдущего заказа для этого текущего заказчика. Выполните это с использованием внешнего ключа, ссылающегося на саму таблицу Заказов. Внешний ключ должен ссылаться также на поле cnum заказчика, обеспечивающее определенную предписанную связь между текущим порядком и ссылаемым. (См. ответы в Приложении A.) |
||