Ограничение FOREIGN KEY
Внешний ключ - это столбец или набор столбцов в одной таблице, которые в точности соответствуют столбцу или набору столбцов, определенных как ограничение PRIMARY KEY или ONIQUE в другой таблице. В своей простейшей форме внешний ключ реализует необязательное отношение один-ко-многим.
! ! !
ПРИМЕЧАНИЕ. Необязательное отношение существует, когда отношение возможно в формальной структуре, но не является необходимым. То есть родитель- ский экземпляр может существовать без каких-либо ссылок на него со стороны дочернего элемента, но, если оба существуют, оба подчиняются ограничениям. В противоположность этому существуют обязательные отношения. Обязательные отношения обсуждаются позже в этой главе.
. ! .
Стандартная модель объект-отношение описывает простое отношение один-ко- многим, между двумя сущностями, как показано на рис. 17.1.
Рис. 17.1. Модель объект-отношение
Если мы реализуем такую модель между двумя таблицами PARENT и CHILD, то строки в таблице CHILD зависят от существования связанной строки из PARENT. Ограничение FOREIGN KEY в Firebird осуществляет это отношение следующими способами:
* требуется, чтобы значение столбца внешнего ключа в таблице CHILD (CHILD.PARENT ID) могло быть связано с соответствующим значением уникального ключа (в нашем случае, первичного ключа) в таблице PARENT (PARENT, ID);
* по умолчанию запрещено удаление строки PARENT или изменение значения уникального ключа, если существуют зависимые строки в CHILD;
* должно быть реализовано отношение, которое предполагалось во время создания ссылки или когда оно в последний раз изменялось;
* по умолчанию допускается пустое значение столбца внешнего ключа. Поскольку невозможно связать пустое значение с чем бы то ни было, такие строки являются зависшими - они не имеют родителя.
Реализация ограничения
При реализации ссылочного ограничения должны быть учтены некоторые предварительные условия. В этом разделе мы рассмотрим очень простой пример. Если вы выполняете разработку в существующем сложном окружении, где действуют привилегии SQL, то вам следует позаботиться о получении привилегии REFERENCES. Об этом сказано в отдельном разделе далее в этой главе.
Родительская структура
Необходимо начать с родительской таблицы и создать управляющий уникальный ключ, на который будет ссылаться зависимая таблица. Обычно это первичный ключ родительской таблицы, хотя это не обязательно. Внешний ключ может ссылаться на столбец или группу столбцов, объединенных ограничением UNIQUE. для целей иллюстрации мы будем использовать первичный ключ:
CREATE TABLE PARENT (
ID BIGINT NOT NULL,
DATA VARCHAR(20),
CONSTRAINT PK_PARENT PRIMARY KEY(ID));
COMMIT;
Дочерняя структура
В дочернюю структуру нам нужно включить столбец PARENT_ID, который в точности соответствует первичному ключу родительской таблицы по типу и размеру (а также по порядку столбцов, если связь выполняется по нескольким столбцам):
CREATE TABLE CHILD (
ID BIGINT NOT NULL,
CHILD_DATA VARCHAR(20),
PARENT_ID BIGINT,
CONSTRAINT PK_CHILD PRIMARY KEY(ID));
COMMIT;
Следующее, что нам нужно сделать, это определить отношение между дочерней и родительской таблицами - создать ограничение внешнего ключа.
Синтаксис определения FOREIGN KEY
Синтаксис определения ссылочной целостности следующий:
FOREIGN KEY (столбец [, столбец ...])
REFERENCES (родительская-таблица [, столбец ...])
[USING [ASC | DESC] INDEX имя-индекса] /* добавлено в версии 1.5 */
[ON DELETE {NO ACTION | CASCADE | SET NULL | SET DEFAULT}]
[ON UPDATE {NO ACTION | CASCADE | SET NULL | SET DEFAULT}]
Определим наш внешний ключ:
ALTER TABLE CHILD
ADD CONSTRAINT FK_CHILD_PARENT
FOREIGN KEY(PARENT_ID)
REFERENCES PARENT(ID);
/* также допустимо REFERENCES PARENT, поскольку ID является первичным ключом таблицы PARENT */
Firebird сохраняет ограничение FK_CHILD_PARENT и создает обычный индекс для столбца (столбцов), перечисленных в качестве аргументов FOREIGN KEY. В Firebird этот индекс будет также назван FK_CHILD_PARENT, если вы не использовали необязательное предложение USING для задания другого имени индекса. В Firebird 1.0.x индекс будет иметь имя INTEG_NN (где NN - некоторое число).
! ! !
ВНИМАНИЕ! Если вы указали убывающий индекс для ограничения первичного или уникального ключа, вы также должны указать USING DESCENDING INDEX для каждого ссылающегося на него внешнего ключа.
. ! .
Наши две таблицы теперь связаны огpаничением формальной ссылочной целостности. Мы можем добавлять новые строки в таблицу PARENT без каких-либо огpаничений:
INSERT INTO PARENT (ID, DATA)
VALUES (1, 'Pareпt No, 1');
При этом существует ограничение для CНILD. Мы можем выполнить следующее:
INSERT INTO CHILD (ID, CHILD_DATA)
VALUES (1, 'Child No. 1');
Поскольку допускающий пустое значение столбец PARENT_ID отсутствует в списке столбцов, в нем будет сохранено значение NULL. Это допускается правилами целостности по умолчанию. Такая строка будет зависшей (или осиротевшей, orphan).
Однако мы получим ошибку ограничения, если попытаемся сделать следующее:
INSERT INTO CHILD(ID, CHILD_DATA, PARENT_ID)
VALUES (2, 'child No, 2', 2);
ISC ERROR CODE:335544466
ISC ERROR MESSAGE:
violation of FOREIGN KEY constraint "FK_CHILD_PARENT" on table "CHILD" (нарушение ограничения "FK_CHILD_PARENT" для внешнего ключа таблицы CHILD)
В таблице PARENT не существует строки, имеющей у первичного ключа значение 2, следовательно, ограничение не позволит выполнить добавление.
Оба следующих действия допустимы:
UPDATE CHILD
SET PARENT_ID = 1
WHERE ID = 1;
COMMIT;
/**/
INSERT INTO CHILD (ID, CHILD_DATA, PARENT_ID)
VALUES (2, 'Child No.2', 1) ;
COMMIT;
Теперь строка из PARENT со значением ID = 1 имеет две дочерние строки. Это классическая структура главная-подчиненная - простая реализация отношения один-ко- многим. Для защиты целостности данного отношения правила по умолчанию не позволят выполнить следующее:
DELETE FROM PARENT WHERE ID = 1;
Действия триггеров по изменению правил целостности
Очевидно, что правила целостности применяются, когда происходят изменения данных, влияющих на отношение. При этом правила по умолчанию не всегда подходят для всех требований. Мы можем захотеть перекрыть правило, которое позволяет создавать зависшие дочерние строки или сделать их зависшими при установке значения их внешнего ключа в NULL. Если для наших бизнес-правил требуется запрет удаления родительской строки, имеющей дочерние строки, мы можем пожелать, чтобы Firebird позаботился об этой проблеме автоматически. Язык SQL в Firebird позволяет сделать это с помощью необязательных автоматических действий триггеров:
[ON DELETE {NO ACTION | CASCADE | SET NULL | SET DEFAULT}]
[ON UPDATE {NO ACTION | CASCADE | SET NULL | SET DEFAULT}]
Автоматические действия триггеров
Firebird предоставляет необязательные стандартные события DML - ON UPDATE и ON DELETE, - используемые для изменения правил ссылочной целостности. События DML и автоматическое поведение совместно определяют действия для триггера - какие действия должны быть выполнены для зависимой таблицы при изменении или удалении соответствующего ключа в родительской таблице. Определение действий включают каскадные изменения в связанной через внешний ключ таблице (таблицах).
Семантика действий триггера
NO ACTION
Поскольку это действие триггера по умолчанию, ключевое слово может быть - и часто бывает - опущено. Операция DML над родительским первичным ключом не изменяет внешний ключ и потенциально может привести к ошибке операции над родительской таблицей.
ON UPDATE CASCADE
В зависимой таблице внешний ключ, соответствующий старому значению первичного ключа, изменяется на новое значение первичного ключа.
ON DELETE CASCADE
В зависимой таблице удаляются строки с соответствующим значением ключа.
SET NULL
Внешний ключ, соответствующий старому значению родительского первичного ключа, устанавливается в NULL- зависимые строки становятся зависшими. Ясно, что это действие триггера не может быть применено, если столбец внешнего ключа не допускает пустых значений.