Ссылочная целостность
Целостность сущностей
Целостная часть реляционной модели данных
Реляционная модель данных определяет два базовых требования целостности, которые поддерживаются любой РСУБД:
- требование целостности сущностей,
- требование целостности по ссылкам.
Объекту или сущности реального мира в реляционной БД соответствует кортеж отношения. Требование целостности сущностей состоит в следующем:
Любой кортеж любого отношения должен быть отличим от любого другого кортежа этого же отношения.
Это требование означает, что каждое отношение должно иметь первичный ключ. Это требование выполняется автоматически, если в системе не нарушаются базовые свойства отношений.
Кроме этого, могут быть установлены и следующие ограничения:
- UNIQUE – уникальность значения атрибутов; определяет альтернативные ключи отношения;
- NOT NULL – обязательность значения; при вставке новых или модификации существующих элементов отношения значения соответствующих атрибутов должны быть заданы;
- CHECK (условие) – допустимые значения атрибутов; вставляемые значения должны удовлетворять указанному условию.
Например, отношение ДЕТАЛЬ на SQL для СУБД MS SQL Server может быть определено следующим образом:
CREATE TABLE P(
P_ID SMALLINT IDENTITY(1,1) CONSTRAINT P_PK PRIMARY KEY, --1
PNAME VARCHAR(20) NOT NULL CONSTRAINT P_UQ_01 UNIQUE, --2
PRICE MONEY NOT NULL CONSTRAINT P_CH_01 CHECK(PRICE > 0) --3
)
Пояснения:
1 – определено ограничение первичного ключа (ограничение PRIMARY KEY, имя ограничения – P_PK), значения которого должны устанавливаться автоматически, начиная с 1 и с шагом 1 (т.е. в первой записи будет установлено значение 1, во второй – значение 2 и т.д.)
2 – определены ограничения уникальности (имя ограничения – P_UQ_01) и обязательности значения;
3 – определено условие (имя ограничения – P_CH_01): значение атрибута должно быть строго положительным.
При определении ограничений полезно задавать их имена, которые обычно строятся по следующему правилу: в имя ограничения включается имя таблицы, затем условное название типа ограничения и номер ограничения данного типа, если их в данной таблице может быть несколько.
Ссылочные ограничения целостности в РМД представляют собой условия, накладываемые на сосуществование кортежей в связанных отношениях. Как уже говорилось, в реляционной БД связи между отношениями представляются с помощью внешних ключей. Вернемся к примеру, в котором рассматривались отношения ОТДЕЛ и СОТРУДНИК:
ОТДЕЛ ( Номер отдела, Название (АК) )
СОТРУДНИК Номер сотрудника, Имя, Год рождения, Номер отдела (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 – определяется ограничение первичного ключа; так как в данном примере первичный ключ составной, определение первичного ключа дается как ограничение уровня таблицы (ограничение внешнего ключа в данном примере, устанавливаемое только для одного атрибута, является ограничением уровня атрибута).