|
|
Книги-onlineГлава 19. Глава 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, так что оба их значения внешнего ключа совпадают с одним и тем же родительским ключом, что очень хорошо. Значение внешнего ключа должно ссылаться только на одно значение родительского ключа, зато на одно значение родительского ключа может ссылаться с помощью любого количества значений внешнего ключа. ОГРАНИЧЕНИЕ ВНЕШНЕГО КЛЮЧА/FOREIGN KEYSQL поддерживает справочную целостность с ограничением FOREIGN KEY. Хотя ограничение FOREIGN KEY это новая особенность в SQL, оно ещё не обеспечивает его универсальности. Кроме того, некоторые его реализации более сложны, чем другие. Эта функция должна ограничивать значения, которые вы можете ввести в вашу БД, чтобы заставить внешний ключ и родительский ключ соответствовать принципу справочной целостности. Одно из действий ограничения Внешнего Ключа - отбрасывание значений для полей, ограниченных как внешний ключ, который ещё не представлен в родительском ключе. Это ограничение также воздействует на вашу способность изменять или удалять значения родительского ключа (мы будем обсуждать это позже в этой главе). КАК МОЖНО ПРЕДСТАВИТЬ ПОЛЯ В КАЧЕСТВЕ ВНЕШНИХ КЛЮЧЕЙВы используете ограничение FOREIGN KEY в команде CREATE TABLE (или ALTER TABLE), содержащей поле, которое вы хотите объявить внешним ключом. Вы даёте имя родительскому, ключу на которое вы будете ссылаться внутри ограничения FOREIGN KEY. Помещение этого ограничения в команду - такое же, что и для других ограничений, обсуждённых в предыдущей главе. Подобно большинству ограничений, оно может быть ограничением таблицы или столбца, в форме таблицы, позволяющей использовать многочисленные поля как один внешний ключ. ВНЕШНИЙ КЛЮЧ КАК ОГРАНИЧЕНИЕ ТАБЛИЦЫСинтаксис ограничения таблицы 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); Имейте в виду, что, при использовании ALTER TABLE вместо CREATE TABLE для применения ограничения FOREIGN KEY, значения, которые вы указываете во внешнем ключе и родительском ключе, должны быть в состоянии справочной целостности. Иначе команда будет отклонена. Хотя ALTER TABLE очень полезна из-за её удобства, вы должны будете в вашей системе, по возможности, каждый раз сначала формировать структурные принципы, типа справочной целостности. ВНЕШНИЙ КЛЮЧ КАК ОГРАНИЧЕНИЕ СТОЛБЦОВВариант ограничения столбца ограничением FOREIGN KEY по-другому называется ссылочное ограничение (REFERENCES), так как оно фактически не содержит в себе слов FOREIGN KEY, а просто использует слово REFERENCES и далее имя родительского ключа, как здесь: 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) не были введены в родительский ключ. Следовательно, вы должны убедиться, что все поля, которые используются как родительские ключи, имеют или ограничение PRIMARY 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 в таблице Заказов единым
внешним ключом, это гарантия того, что для каждого заказчика, содержащегося в
заказах, продавец, кредитующий этот заказ - тот же, что и указанный в таблице
Заказчиков. Создание внешнего ключа таким способом поддерживает целостность БД (даже если при этом вам будет запрещено внутреннее прерывание по ошибке) и кредитование любого продавца, отличного от того, который назначен именно этому заказчику. С точки зрения поддержания целостности БД, внутренние прерывания (или исключения), конечно же, нежелательны. Если вы их допускаете и, в то же время, хотите поддерживать целостность вашей БД, вы можете объявить поля snum и cnum в таблице Заказов независимыми внешними ключами этих полей в таблице Продавцов и таблице Заказчиков, соответственно. Фактически не обязательно использовать поля snum в таблице Заказов, как это делали мы, хотя это полезно было сделать для разнообразия. Поле cnum, связывая каждый заказ заказчиков в таблице Заказчиков и в таблице Заказов, должно всегда быть общим, чтобы находить правильное поле snum для данного заказа (не разрешая никаких исключений). Это означает, что мы записываем фрагмент информации - какой заказчик назначен к какому продавцу - дважды, и нужно будет выполнять дополнительную работу чтобы удостовериться, что обе версии согласуются. Если мы не имеем ограничения внешнего ключа, как сказано выше, эта ситуация будет особенно проблематична, потому что каждый порядок нужно будет проверять вручную (вместе с запросом), чтобы удостовериться, что именно соответствующий продавец кредитовал каждую соответствующую продажу. Наличие такого типа информационной избыточности в вашей БД называется деморализация (denormalization), что нежелательно в идеальной реляционной базе данных, хотя практически и может быть разрешено. Деморализация может заставить некоторые запросы выполняться быстрее, поскольку запрос в одной таблице выполняется всегда значительно быстрее, чем в объединении. ДЕЙСТВИЕ ОГРАНИЧЕНИЙКак ограничения воздействуют на возможность и невозможность использования команды модификации DML? Для полей, определённых как внешние ключи, ответ довольно простой: любые значения, которые вы помещаете в эти поля командой INSERT или UPDATE, должны уже быть представлены в их родительских ключах. Вы можете помещать пустые (NULL) значения в эти поля, несмотря на то что значения NULL непозволительны в родительских ключах, если они имеют ограничение NOT NULL. Вы можете удалять (DELETE) любые строки с внешними ключами, не используя родительские ключи вообще. Поскольку затронут вопрос об изменении значений родительского ключа, ответ, по определению ANSI, ещё проще, но, возможно, несколько более ограничен: любое значение родительского ключа, на который ссылаются с помощью значения внешнего ключа, не может быть удалено или изменено. Это означает, например, что вы не можете удалить заказчика из таблицы Заказчиков, пока он ещё имеет заказы в таблице Заказов. В зависимости от того, как вы используете эти таблицы, это может быть или желательно, или хлопотно. Однако это, конечно, лучше, чем иметь систему, которая позволит вам удалить заказчика с текущими заказами и оставить таблицу Заказов ссылающейся на несуществующих заказчиков. Смысл этой системы ограничения в том, что создатель таблицы Заказов, используя таблицу Заказчиков и таблицу Продавцов как родительские ключи, может наложить значительные ограничения на действия в этих таблицах. По этой причине вы не сможете использовать таблицу которой вы не распоряжаетесь (т.е. не вы её создавали и не вы являетесь её владельцем), пока владелец (создатель) этой таблицы специально не передаст вам на это право (что объясняется в Главе 22). Имеются некоторые другие возможные действия изменения родительского ключа, которые не являются частью ANSI, но могут быть найдены в некоторых коммерческих программах. Если вы хотите изменить или удалить текущее ссылочное значение родительского ключа, имеются три возможности: * Вы можете ограничить или запретить изменение (способом ANSI), обозначив, что изменения в родительском ключе ограничены. * Вы можете сделать изменение в родительском ключе и тем самым сделать изменения во внешнем ключе автоматическим, что называется каскадным изменением. * Вы можете сделать изменение в родительском ключе и установить внешний ключ в NULL автоматически (полагая, что NULL разрешены во внешнем ключе), что называется пустым изменением внешнего ключа. Даже в пределах этих трёх категорий вы можете не захотеть обрабатывать все
команды модификации таким способом. INSERT, конечно, к делу не относится. Она
помещает новые значения родительского ключа в таблицу, так что ни одно из этих
значений не может быть вызвано в данный момент. Однако вы можете захотеть
позволить модификациям быть каскадными, но без удалений, и наоборот. Фактические возможности вашей системы должны строго соответствовать стандарту 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); Если вы теперь попробуете удалить 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. ВНЕШНИЕ КЛЮЧИ, КОТОРЫЕ ССЫЛАЮТСЯ
|
|