Материал: Graber_Martin_-_SQL_dlya_prostykh_smertnykh_-_2014

Что происходит 11ри вьтол11ении ко.нанды обновления

сделать ограничение таблицы UNIQUE для двух полей таблицы Customers, несмотря на то, что оно не является необходимым для самой этой таблицы. Поскольку поле cnum в таблице имеет ограничение PRIMARY КЕУ, оно будет уникальным в любом случае, и, следовательно, невозможно получить неуникальную комбинацию поля cnum с любым

другим полем.

При таком определении внешнего ключа поддерживается целостность базы дан­ ных, но теряется возможность сделать исключения относительно связей между про­ давцами и покупателем. Гарантия от ошибок часто означает устранение возможности делать какие-либо исключения, и решение об их необходимости должно приниматься на уровне управления, а не базы данных. С точки зрения управления целостностью базы данных исключения нежелательны. Но если вы хотите их разрешить и сохранить при этом целостность, можно объявить snum и cnum в таблице Orders независимыми внешними ключами одноименных полей в таблицах Salespeople и Customers соответ­

ственно.

Использование поля snum в таблице Orders не является необходимым, хотя полез­ но с точки зрения рассмотрения примеров. Поле cnum связывает каждый заказ с поку­ пателем в таблице Customers, а таблицы Orders и Customers всегда могут быть связаны для поиска правильного значения snum для данного заказа (если предположить, что никакие исключения не разрешены). Часть информации - какой покупатель какому

продавцу назначен - получена дважды, и можно выполнить ряд операций с целью

удостовериться в том, что две версии согласуются. Если бы не было ограничений внешнего ключа, ситуация была бы проблематичной, поскольку каждая заявка должна бьша бы выбираться вручную (с помощью запроса), чтобы убедиться в том, что соот­

ветствующий продавец связан с соответствующим заказом. Наличие такого рода из­

быточной информации в базе данных называется денормализацией (denormalization) и является нежелательной в идеальной реляционной базе данных, хотя в практических ситуациях могут быть причины для ее разрешения. Денормализация дает возмож­ ность более быстрого выполнения ряда запросов, поскольку запрос к единственной таблице выполняется гораздо быстрее, чем соединение.

Действие ограничений

Как ограничения влияют на то, что можно и чего нельзя делать с помощью команд

обновления DML? Что касается подей, определенных в качестве внешнего ключа, ответ более или менее ясен: любые значения, которые вводятся в них с помощью команды INSERT или UPDATE, должны быть уже представлены в их родительских ключах.

Можно вводить NULL-значения в эти поля несмотря на то, что NULL-значения не раз­

решены в родительских ключах, если они не имеют NOT NULL-ограничений. Можно удалить (выполнить команду DELETE) любую строку с внешним ключом, не оказывая

никакого действия на родительские ключи.

Что касается изменений значений родительского ключа, то ответ, определенный ANSI, прост, но слишком ограничен: любое значение родительского ключа, на кото­ рый есть ссылка внешнего ключа, не может быть удалено или изменено. Это означает, что нельзя удалить покупателя из таблицы Customers, пока он имеет заказы в таблице

205

Глава 19. Поддер;нска целостности даиных

Orders. В зависимости от использования таблиц, это может оказаться либо желатель­ но, либо хлопотно. Но намного предпочтительнее вариант, при котором было бы раз­ решено исключить покупателя, имеющего текущие заказы, и оставить таблицу Customers, ссылающейся на несуществующих покупателей.

Из этого следует, что создатель таблицы Orders, используя таблицы Customers и Salespeople как родительские ключи, вводит значительные ограничения на их ис­ пользования. По этой причине нельзя использовать таблицу в качестве родительско­ го ключа невладельцу таблицы, хотя владелец этой таблицы может предоставить

такое право.

Существует несколько возможных способов изменения родительского ключа, не являющихся частью ANSI, их можно найти в некоторых коммерческих продуктах. Для

изменения или исключения значений родительского ключа, на который имеется ссыл­

ка, есть три основные возможности:

• Можно ограничить или запретить изменение (согласно ANSI), что означает, что изменение родительского ключа ограничено (restricted).

• Можно изменить родительский ключ и автоматически получить изменения внешнего ключа; это означает, что изменения выполняются каскадно

(cascades).

•Можно изменить родительский ключ, и тем самым установить значения внеш­ него ключа в NULL автоматически (предполагается, что NULL-значения разре­

шены во внешнем ключе); в этом случае говорят, что внешний ключ изменяется

в nиll.

Нежелательно, чтобы все команды обновления в каждом из этих трех случаев вы­ полнялись одинаково. На INSERT это, конечно, не распространяется. Если она вводит новые значения родительского ключа в таблицу, значит ни на одно из этих значений еще нет ссылок. Может понадобиться разрешить выполнение изменений каскадом, а не удалений или наоборот. Тогда лучше всего иметь возможность определять любую из трех операций независимо для команд UPDATE и DELETE. Следовательно, надо ссылаться на эффекты обновления (update effects) и эффекты удаления (delete f!f!ects), которые фиксируют, что происходит при выполнении команд UPDATE или DELETE для родительского ключа. Эти эффекты, упоминавшиеся ранее, определяются ключе­

выми словами RESTRICTED, CASCADES, NULLS.

Реальные возможности данной системы распространяются от ограничений ANSI стандарта - эффекты обновления и удаления автоматически ограничены - до бо­ лее идеальной ситуации, рассмотренной ранее. Приведем несколько примеров того, что дают эффекты обновления и удаления. Стандартный синтаксис обновления и удаления отсутствует, поскольку речь идет о нестандартных характеристиках. Син­

таксис, используемый здесь, весьма прост и служит для иллюстрации действия этих

эффектов.

Предположим, есть причина для изменения значения поля snum таблицы Salespeople по случаю, например, изменения районов действия продавцов. (Изменять заведенный порядок назначения первичных ключей на практике не рекомендуется.

206

Что происходит при вы11ол11е11ии кома11ды обновления

Один из доводов в пользу этого: они не должны изменяться.) При изменении номера продавца или при удалении сведений о нем из базы данных необходимо сохранить связь со всеми его покупателями или сохранить всех его покупателей, более того, удо­

стовериться, что им назначен новый продавец. Для этого следует определить д-1я

UPDATE эффект CASCADES, а для DELETE - эффект RESTRICTED.

Если попытаться удалить Рее\ из таблицы Salespeople, то команда не будет выпол­ нена, пока значения поля snum для покупателей Hoffman и Clemens не будут изменены таким образом, чтобы они обслуживались другим продавцом. С другой стороны, мож­ но изменить значение поля snum для Рее\ на 1009, при этом соответствующие поля для Hoffman и Clemens изменятся автоматически.

Третий эффект заключается в NULL-значениях. Когда продавцы уходят из компа­ нии, их текущие заказы остаются без адресатов. С другой стороны, необходимо авто­ матически отменить все заказы тех покупателей, обслуживание которых прекращено. Изменения номеров продавца или покупателя могут просто передаваться для выпол­ нения. Создается таблица Orders, чтобы предусмотреть все перечисленные действия:

Для того, чтобы в DELETE отрабатывалось действие NULLS для таблицы Salespeople, следует удалить ограничение NOT NULL для поля snum.

207

Глава 19. Поддержка целостности данных

Внешние ключи, ссылающиеся на те самые таблицы, в которых они определены

Как упоминалось ранее, ограничение FOREIGN КЕУ может содержать имя своей собственной таблицы в качестве имени родительской таблицы. Такая черта на практи­ ке является весьма полезной. Предположим, есть таблица Employees с полем, назван­ ным "manager". Это поле содержит номер менеджера служащего, а поскольку каждый менеджер является и служащим, он также представлен в этой таблице. Создадим таб­ лицу, в которой поле empno (employee number - номер служащего) объявлено первич­ ным ключом, а поле manager (менеджер, управляющий) - внешним ключом,

ссылающимся на него:

(Поскольку внешний ключ ссьшается на первичный ключ таблицы, перечисление ат­ рибутов столбца можно опустить.) Таблица может содержать, например, такие данные:

Каждый служащий в этой таблице, кроме Atali, ссылается на другого служащего как на своего менеджера. Atali, занимающий высший пост в фирме, должен иметь в поле manager значение NULL. Здесь проявляется еще один принцип ссылочной цело­ стности. Внешний ключ, ссылающийся на свою собственную таблицу, должен допус­ кать NULL-значения.

Этот принцип должен соблюдаться даже в том случае, когда внешний ключ ссыла­ ется на собственную таблицу опосредованно (не непосредственно), т.е. ссылается на другую таблицу, которая, в свою очередь, ссылается на ту таблицу, в которой был оп­ ределен внешний ключ. Например, таб.1ица Salespeople содержит дополнительное поле, которое ссылается на таблицу Customers, таким образом таблицы ссылаются друг на друга; этот факт отражен в следующем предложении CREATE TABLE:

CREATE TABLE Salespeople

208

Итоги

Это называется цикличностью (ci1·culaгity) или перекрестной ссылкой (cгoss 1·eferencing). SQL поддерживает эту процедуру теоретически, но на практике могут воз­ никнуть проблемы. Например, какая бы из этих двух таблиц ни создавалась, первая бу­ дет ссьшаться на таблицу, которой еще нет. В интересах поддержки цикличности SQL

действительно допускает такую ситуацию, но ни одну из этих таблиц нельзя использо­

вать, пока не созданы обе. С другой стороны, если эти две таблицы созданы различными пользователями, проблема становится еще сложнее. Цикличность может быть полез­ ным инструментом, но здесь не обойтись без неоднозначных толкований и риска. В ча­

стности, предыдущий пример не очень выразителен: он ограничивает продавца одним

покупателем, и здесь можно обойтись без цикличности. Цикличность необходимо ис­ пользовать осторожно, детально изучив, как данная система выполняет действия обнов­ ления и удаления, а также поддержку привилегий и обработку транзакций; все это должно предшествовать созданию цикличной системы, поддерживающей ссылочную

целостность.

Итоги

Основная идея ссылочной целостности заключается в том, что все значения внеш­ них ключей отсылают к определенной строке родительского ключа. Это означает, что каждое значение внешнего ключа должно быть представлено в родительском ключе один и только один раз. Как только значение размещается во внешнем ключе, роди­ тельский ключ проверяется для того, чтобы убедиться в том, что такое значени5-в нем присутствует; в противном случае команда отвергается. Родительский ключ должен иметь ограничение PRIMARY КЕУ или UNIQUE для гарантии того, что значение не представлено более чем один раз. Попытки изменить значение родительского ключа на значение, присутствующее во внешнем ключе, отвергаются. Система, однако, мо­ жет позволять иметь в качестве значения внешнего ключа NULL-значение или новое значение родительского ключа, а также задавать каждый из этих вариантов независи­ мо для команд UPDATE и DELETE. На этом описание команды CREATE TABLE за­

вершается. Далее будут рассмотрены другие команды типа CREATE.

Из главы 20 вы узнаете о представлениях - объектах базы данных, которые выгля­ дят и функционируют как таблицы, но по существу являются результатами запросов.

Некоторые из ограничений присущи исключительно представлениям, и, ознакомив­

шись с содержанием следующих трех глав, вы сможете более четко формулировать ог­ раничения в соответствии с потребностями.

209