Firebird РУКОВОДСТВО РАЗРАБОТЧИКА БАЗ ДАННЫХ
Шрифт:
выполнено удаление строки главной таблицы, мы возвращаемся назад и удаляем подчиненную строку.
CREATE TRIGGER BD_MASTER FOR MASTER
ACTIVE BEFORE DELETE
AS
BEGIN
UPDATE DETAIL
SET MASTER_ID = NULL,
TEMP_FK = OLD.ID
WHERE MASTER_ID = OLD.ID;
END ^
/* */
CREATE TRIGGER AD_MASTER FOR MASTER
ACTIVE AFTER DELETE AS
BEGIN
DELETE FROM DETAIL
WHERE TEMP_FK = OLD.ID;
END ^
COMMIT ^
SET TERM; ^
К сожалению, этот пример вряд ли удовлетворит всем требованиям обязательных отношений. Обычно необходимо рассмотреть некоторые другие факторы в терминах требований бизнес-правил
Поддержка ссылочной целостности
Формально - или декларативно - ограничения ссылочной целостности должны использоваться везде, где они практически нужны. Проверка, выполняющаяся при поддержке формальных ограничениях ссылочной целостности, осуществляется внутренне триггерами. Если вы хотите расширить действия по ссылочной целостности, вам нужно создать для этого триггеры.
Некоторые твердолобые люди годами разрабатывают программы под Firebird и InterBase, избегая декларативной ссылочной целостности, и используют для этого триггеры. Не существует технических причин в любой версии Firebird исключать декларативную ссылочную целостность, если она вам нужна- это работает очень хорошо и не требует многих ресурсов.
Однако декларативная ссылочная целостность требует внешнего ключа, который в свою очередь требует обязательного индекса. В Firebird пока не существует способа поддерживать внешние ключи без обязательного индекса. Существует ситуация, достаточно общая, чтобы обратить на нее внимание, когда индекс для внешнего ключа оказывается весьма плохим с точки зрения производительности в запросах, включающих данную таблицу. Здесь следует исключить формальные ссылочные отношения. Такая ситуация возникает, когда при проектировании появляются таблицы, часто называемые таблицами "соответствия", "системными" или "управляющими".
Таблица соответствия (lookup) или управляющая таблица обычно является статичной таблицей с небольшим количеством строк, которая может быть использована похожим образом в нескольких различных контекстах. Она содержит небольшой первичный ключ и поле описания, коэффициент для вычисления или некоторое правило, к которым нужно обратиться процессу. Примерами являются таблицы налогов, типы счетов, типы транзакций, коды причин и т.д. Процесс нормализации будет нарушен, если системная таблица, связанная с другими таблицами (обычно со многими), будет сохранять ключи соответствия в пользовательской таблице. Поскольку одна строка в таблице соответствия предоставляет информацию для многих строк, строгое соблюдение правил реляционного анализа часто приводит к внешним ключам, помещенным в столбцы ключей соответствия в пользовательских таблицах.
Это полностью допустимый и стандартный способ использования отношений - что мы можем сделать без него? Однако существует тенденция использования небольшого количества значений ключей соответствия в большой динамичной пользовательской таблице. Такие большие таблицы часто содержат немалое количество подобных ключей соответствия в виде внешних ключей, а с ними и множество автоматических нехороших индексов, которые не могут быть удалены. Результатом небольшого количества значений в большом индексе может стать увеличение размера индекса, который становится все менее и менее селективным по мере роста таблицы. По природе индексирования в Firebird понятно, что такие индексы соответствия могут попросту убить эффективность запроса. Обсуждение этой проблемы см. в разд. "Тема оптимизации" главы 18.
Индексы, поддерживающие внешние ключи, являются обязательными и могут быть удалены только при удалении этого ограничения. Кроме того, с удалением такого ограничения вы теряете защиту с автоматическими триггерами ссылочной
Этот раздел посвящен специальному виду отношений, системным соответствиям (lookup), которые обычно не поддерживаются в декларативной ссылочной целостности. Используемая здесь терминология соответствует требованиям подобного случая, поскольку установка полностью контролируемой пользователем ссылочной целостности является нецелесообразной для обычных отношений главная-подчиненная. Рис. 31.1 иллюстрирует эту ситуацию. Инициатор запроса, которым может быть любая таблица, имеет ключ соответствия, который указывает на одну, уникально определяемую ключом строку в таблице соответствия. Значение этой строки предоставляется таблице соответствия по запросу.
Рис. 31.1. Отношение-инициатор запроса - таблица соответствия
Для поддержания ссылочной целостности нам нужен триггер, который предоставит набор мер безопасности пользователям таблицы соответствия (инициаторы запроса) так же, как и декларативная ссылочная целостность обеспечивает меры безопасности для защиты зависимостей главная-подчиненная [124] .
* Строка соответствия не может быть удалена, если на нее есть ссылки. Для этого нам нужен триггер BEFORE DELETE К таблице соответствия для проверки такой ситуации и при необходимости выдачи исключения и остановке операции.
124
Поскольку проверки Foreign Key (FK) выполняются вне транзакций, а пользовательские триггеры работают всегда в контексте транзакций, полноценно заменить FK триггерами невозможно - из-за ограничений "видимости" триггер будет видеть старые данные, которые уже изменены. Приведенная схема будет работать, только если для справочной таблицы наложен ряд ограничений по удалению и изменению записей.
– Прим. науч. ред.
* Мы должны обеспечить осуществление правила, чтобы требуемый запросом ключ соответствия соответствовал ключу в таблице. Наше правило может допускать или не допускать для ключа соответствия пустое значение.
* Нам может понадобиться правило, чтобы статичное значение никогда не менялось. В таблице налогов, например, один и тот же код налога (внешний) может быть связан с различными суммами и формулами в разные годы. Вероятно, только главному бухгалтеру будет позволено изменять строку соответствия.
* Триггер BEFORE UPDATE для инициатора запроса потребуется для обработки сложного правила, такого как описано в предыдущем пункте для проверки дат и, возможно, других критериев для осуществления правила и выбора корректного ключа.
Предположим, мы имеем следующие две таблицы:
CREATE TABLE LOOKUP (
UQ_ID SMALLINT NOT NULL UNIQUE,
VALUE1 VARCHAR(30) NOT NULL,
VALUE2 CHAR(2) NOT NULL,
START_DATE DATE,
END_DATE DATE) ;
COMMIT;
/* */
CREATE TABLE REQUESTOR (
ID INTEGER NOT NULL PRIMARY KEY,
LOOKUP_ID SMALLINT,
DATA VARCHAR(20)
TRANSAC_DATE TIMESTAMP NOT NULL) ;
COMMIT;
Теперь мы перейдем к установлению правил существования для двух таблиц. Мы планируем использовать исключения для остановки событий DML, которые нарушают целостность. Следовательно, вначале мы создадим эти исключения: