Ссылочная целостность

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

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

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

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

Рассмотрим отношения:

 

СОТРУДНИК (Номер_сотрудника, Фамилия, Имя, Улица, Город, Индекс, Должность, Пол, Зарплата)

Primarv Key Номер_сотрудника

 

ОБЪЕКТ_НЕДВИЖИМОСТИ(Номер_объекта, адрес, тип)

Primarv Key Номер_объекта

 

Связь СОТРУДНИК «отвечает за» ОБЪЕКТ_НЕДВИЖИМОСТИ) типа 1:М.

Первичный ключ отношения СОТРУДНИК — атрибут Номер_сотрудника — является внешним ключом отношения ОБЪЕКТ_НЕДВИЖИМОСТИ. Рассмотрим следующие ситуации.

Случай 1. Вставка новой строки в дочернее отношение (ОБЪЕКТ_НЕДВИЖИМОСТИ). Для обеспече­ния ссылочной целостности необходимо убедиться, что значение атрибута внеш­него ключа Номер_сотрудникановой строки отношения ОБЪЕКТ_НЕДВИЖИМОСТИ равно пустому значению либо некоторому конкретному значению, присутствующему в одной из строк от­ношения СОТРУДНИК.

Случай 2. Удаление строки из дочернего отношения (ОБЪЕКТ_НЕДВИЖИМОСТИ). При удалении строки из дочернего отношения никаких нарушений ссылочной целостности не происходит.

Случай 3. Обновление внешнего ключа в строке дочернего отношения (ОБЪЕКТ_НЕДВИЖИМОСТИ). Этот случай подобен случаю 1. Для сохранения ссылочной целостности необходимо убедиться, что атрибут Номер_сотрудника в обновленной строке отношения ОБЪЕКТ_НЕДВИЖИМОСТИ со­держит либо пустое значение, либо некоторое конкретное значение, присутст­вующее в одной из строк отношения СОТРУДНИК.

Случай 4. Вставка строки в родительское отношение (СОТРУДНИК). Вставка строки в родительское отношение (СОТРУДНИК) не может вызвать нарушения ссылочной целост­ности. Добавленная строка просто становится родительским объектом, не имею­щим дочерних объектов. В данном случае это означает, что новый работник еще не отвечает ни за какие объекты недвижимости

Случай 5. Удаление строки из родительского отношения (СОТРУДНИК). При удалении строки из родительского отношения ссылочная целостность будет нарушена в том случае, если в дочернем отношении будут существовать строки, ссылающиеся на удаленную строку родительского отношения. Другими словами, ссылочная цело­стность будет нарушена, если удаленный работник отвечал за один или больше объектов недвижимости.

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

NO ACTION	Удаление строки из родительского отношения запрещается, если в дочернем отношении существует хотя бы одна ссылающаяся на нее стро­ка. В нашем случае это звучит так: "Нельзя удалить сведения о работнике, отвечающем в настоящий момент хотя бы за один объект недвижимости".
CASCADE	При удалении строки из родительского отношения автоматически удаляются все ссылающиеся на нее строки дочернего отношения. Если лю­бая из удаляемых строк дочернего отношения выступает в качестве роди­тельской стороны в некоторой другой связи, то операция удаления приме­няется ко всем строкам дочернего отношения этой связи,и т.д.. Другими словами, удаление строки родительского отношения автоматически рас­пространяется на любые дочерние отношения. В нашем случае это звучит так: "Удаление работника автоматически влечет за собой удаление сведе­ний обо всех объектах недвижимости, которыми он занимался". Очевидно, что в данном примере подобная стратегия неприемлема.
SET NULL	При удалении строки из родительского отношения во всех ссы­лающихся на нее строках дочернего отношения в атрибут внешнего ключа записывается пустое значение. Следовательно, удаление строк из родитель­ского отношения вызовет занесение пустого значения в соответствующий атрибут строк дочернего отношения. В нашем случае это звучит так: "При удалении работника все объекты недвижимости, которыми он занимался, остаются без отвечающего за них работника". Эта стратегия может исполь­зоваться только в тех случаях, когда в атрибут внешнего ключа дочернего отношения разрешается помещать пустые значения.
SET DEFAULT	При удалении строки из родительского отношения в атрибут внешнего ключа всех ссылающихся на нее строк дочернего отношения авто­матически помещается значение, указанное для этого атрибута как значение по умолчанию. Таким образом, удаление строки из родительского отношения вызывает помещение принимаемого по умолчанию значения в атрибут внешнего ключа всех строк дочернего отношения, ссылающихся на удален­ную строку. В нашем случае это будет звучать так: "При удалении работника все объекты недвижимости, которыми он занимался, передаются некоторому другому работнику (например, руководителю отделения)". Эта стратегия применима только в тех случаях, когда атрибуту внешнего ключа дочернего отношения назначено некоторое значение, принимаемое по умолчанию.
NO CHECK	При удалении строки из родительского отношения никаких дей­ствий по сохранению ссылочной целостности данных не предпринимается.

Случай 6. Обновление первичного ключа в строке родительского отношения (СОТРУДНИК). Если значение первичного ключа некоторой строки родительского отношения будет обновлено, нарушение ссылочной целостности будет иметь место в том случае, если в дочернем отношении существуют строки, ссылающиеся на исходное значение первич­ного ключа. В нашем случае это значит, что работник, для которого было выполнено обновление, в данный момент отвечал за один или более объектов недвижимости. Для сохранения ссылочной целостности может использоваться любая из описанных выше стратегий. При использовании стратегии CASCADE обновление значения первичного ключа в строке родительского отношения будет отображено в любой строке дочернего отношения, ссылающейся на данную строку (каскадным образом).

 

 

На рис. 1 представлена таблица, находящаяся в отношении «один-ко-многим». Связь производится по полю «Номер».

 

Рис. 1. Связанные таблицы

Потеря связей между записями может произойти в нескольких случаях:

- Если будет изменено значение поля связи в родительской таблице без изменения значений в полях дочерней таблицы.

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

Рассмотрим простой случай. Неожиданно у одного из клиентов картинной галереи сменился личный номер. Таким образом, в таблице «Покупатель» у Иванова оказался номер 11. Так как у Иванова А. Г. номер 11, а заказанные им товары находятся под номером 1, то налицо нарушение целостности и достоверности данных. В новом, измененном варианте, Иванов ничего не заказал, а в таблице «Проданные картины» появились «бесхозные данные».

Рассмотрим второй вариант, изображенный на рис. 2. Данные в поле «Номер» были изменены на другие. Таким образом, в полях, имеющих прежнее значение «1», данные стали иметь значение «6». В этой ситуации тоже теряются нужные данные.

 

 

Рис. 2. Нарушение целостности БД

 

Рис. 3. Нарушение целостности БД из дочерней таблицы

 

В обоих примерах имело место нарушение целостности БД, то есть информация, хранящаяся в БД, стала недостоверной из-за искажения связей.

Нарушение ссылочной целостности может возникнуть в нескольких случаях:

- удаление записи из главной таблицы, без удаления связанных записей в дочерней таблице;

- изменение значения поля связи главной таблицы, без изменения ключа дочерней;

- изменение ключа дочерней таблицы без изменения поля связи главной.

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

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

- При удалении записи в родительской таблице обязательно следует удалить все связанные записи.

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