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

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

ОТДЕЛ (Номер отдела, Название (АК))

СОТРУДНИК Номер сотрудника, Имя, Год рождения, Номер отдела (FK))

Атрибут внешнего ключа Номер отдела в отношении СОТРУДНИК позволяет получить полную информацию о том конкретном отделе, в котором работает конкретный сотрудник.

Обычно отношение, в котором определяется внешний ключ, называется дочерним отношением, а отношение, на которое ссылается внешний ключ – родительским отношением. Так, в нашем примере отношение СОТРУДНИК – дочернее, а отношение ОТДЕЛ – родительское.

Требование ссылочной целостности состоит в следующем:

Значение атрибута внешнего ключа в любом кортеже дочернего отношения должно соответствовать значению атрибута первичного ключа в некотором кортеже родительского отношения.

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

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

1. Все операции с дочерним отношением должны удовлетворять требованиям ссылочной целостности:

- при вставке нового элемента этот элемент должен иметь допустимое значение атрибутов внешнего ключа;

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

- при модификации внешнего ключа некоторого элемента этот элемент должен получить допустимое значение атрибутов внешнего ключа.

2. Операции с родительским отношением выполняются в соответствии со следующими правилами:

- вставка нового элемента выполняется без каких-либо ограничений;

- удаление элемента не должно привести к нарушению ссылочной целостности. Если в дочернем отношении существует элемент, ссылающийся на удаляемый элемент родительского отношения, как поступить с ним? Здесь возможны три подхода:

a) restrict – удаление элемента из родительского отношения не выполняется, если в дочернем отношении есть хотя бы один элемент, ссылающийся на удаляемый;

b) cascade – вместе с элементом родительского отношения удаляются все ссылающиеся на него элементы дочернего отношения;

c) set NULL – атрибутам внешнего ключа дочернего отношения присваивается пустое значение (каждая СУБД использует собственный способ задания пустого значения); этот подход возможен, если для атрибутов внешнего ключа дочернего отношения не установлено ограничение обязательности значения;

- модификация значения первичного ключа существующего элемента также не должна привести к нарушению ссылочной целостности. Здесь также возможны те же три подхода:

a) restrict – модификация первичного ключа элемента из родительского отношения не выполняется, если в дочернем отношении есть хотя бы один элемент, ссылающийся на модифицируемый;

b) cascade – вместе с элементом родительского отношения модифицируются значения атрибутов внешнего ключа всех ссылающихся на него элементов дочернего отношения;

c) set NULL – атрибутам внешнего ключа дочернего отношения присваивается пустое значение; этот подход возможен, если для атрибутов внешнего ключа дочернего отношения не установлено ограничение обязательности значения.

Например, отношение связи ПОСТАВКА может быть определено на SQL для MS SQL Server следующим образом. Так как отношение ПОСТАВКА является отношением связи (дочерним отношением) между отношениями ПОСТАВЩИК и ДЕТАЛЬ (родительскими отношениями), прежде чем определять дочернее отношение, необходимо определить родительские.

Определение отношения ДЕТАЛЬ приведено выше.

Отношение ПОСТАВЩИК может быть определено следующим образом:

CREATE TABLE S(

S_ID SMALLINT IDENTITY(1,1) CONSTRAINT S_PK PRIMARY KEY,

SNAME VARCHAR(30) NOT NULL,

ADDRESS VARCHAR(80)

)

Теперь определим отношение связи ПОСТАВКА:

CREATE TABLE SP(

S_ID SMALLINT NOT NULL CONSTRAINT SP_FK_01 REFERENCES S(S_ID), --1

P_ID SMALLINT NOT NULL CONSTRAINT SP_FK_02 REFERENCES P(P_ID), --1

QTY INT NOT NULL CONSTRAINT SP_CH_01 CHECK(QTY > 0),

CONSTRAINT SP_PK PRIMARY KEY(S_ID, P_ID) --2

)

1 – определяется внешний ключ; в ограничении внешнего ключа (ключевое слово REFERENCES) указывается родительская таблица и ее первичный ключ; по умолчанию правило удаления определяется как restrict.

2 – определяется ограничение первичного ключа; так как в данном примере первичный ключ составной, определение первичного ключа дается как ограничение уровня таблицы (ограничение внешнего ключа в данном примере, устанавливаемое только для одного атрибута, является ограничением уровня атрибута).