Читать интересную книгу Firebird РУКОВОДСТВО РАЗРАБОТЧИКА БАЗ ДАННЫХ - Хелен Борри

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

Поскольку это действие триггера по умолчанию, ключевое слово может быть - и часто бывает - опущено[49]. Операция DML над родительским первичным ключом не изменяет внешний ключ и потенциально может привести к ошибке операции над родительской таблицей.

ON UPDATE CASCADE

В зависимой таблице внешний ключ, соответствующий старому значению первичного ключа, изменяется на новое значение первичного ключа.

ON DELETE CASCADE

В зависимой таблице удаляются строки с соответствующим значением ключа.

SET NULL

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

SET DEFAULT

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

* Используется значение по умолчанию, которое существовало в момент создания ограничения FOREIGN KEY. Если значение по умолчанию у столбца будет изменено позже, то значение по умолчанию для действия SET DEFAULT В определении внешнего ключа не будет изменено на новое значение - оно сохранит прежнее значение.

* Если никакое значение по умолчанию не было явно установлено для столбца, то неявным значением по умолчанию будет NULL. В этом случае поведение при SET DEFAULT будет тем же самым, что и при SET NULL.

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

Взаимодействие ограничений

Комбинируя формальное ссылочное ограничение с другими ограничениями целостности (см. главу 16), можно реализовать большинство (если не все) бизнес-правил с высокой степенью точности. Например, ограничение столбца NOT NULL будет корректировать действия и предотвратит появление зависших строк, если это необходимо, тогда как столбец внешнего ключа, который допускает пустые значения, может быть использован для реализации специальных структур данных, таких как деревья (см. разд. "Ссылающиеся на себя отношения").

! ! !

СОВЕТ. Если вам нужно сделать столбец вашего внешнего ключа NOT NULL, создайте "фиктивную" строку родительской таблицы с неиспользуемым значением ключа, например, 0 или -1. Используйте действие SET DEFAULT для эмуляции поведения SET NULL, чтобы сделать значением по умолчанию фиктивное значение ключа.

. ! .

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

Триггеры действий пользователя

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

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

! ! !

СОВЕТ. Селективность индекса довольно подробно обсуждается в главе 18. Если эта тема для вас новая, то это может вас заставить основательно разобраться в данном вопросе до принятия решения реализовывать отношение один- ко-многим в вашей модели данных с использованием формальных ограничений целостности "только потому, что я могу".

. ! .

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

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

Таблицы соответствия и ваша модель данных

Мы часто используем таблицы соответствия (lookup tables) - также называемые управляющими таблицами (control tables) или таблицами определения (definition tables) - для хранения статичных строк, которые могут содержать расширенные тексты, коэффициенты преобразования, а также нечто подобное выходным наборам, часто получаемым в приложениях как списки выбора. Примерами являются таблицы "типов", которые содержат сущности, такие как типы счетов или типы документов, таблицы "коэффициентов", используемые для преобразования валют или вычисления налогов, и таблицы "соответствия кодов", хранящие такие элементы, как коды, соответствующие цвету. Динамичные таблицы связаны с такими статичными таблицами через соответствие ключа первичному ключу статичных таблиц.