Firebird РУКОВОДСТВО РАЗРАБОТЧИКА БАЗ ДАННЫХ

Это полностью допустимый и стандартный способ использования отношений - что мы можем сделать без него? Однако существует тенденция использования небольшого количества значений ключей соответствия в большой динамичной пользовательской таблице. Такие большие таблицы часто содержат немалое количество подобных ключей соответствия в виде внешних ключей, а с ними и множество автоматических нехороших индексов, которые не могут быть удалены. Результатом небольшого количества значений в большом индексе может стать увеличение размера индекса, который становится все менее и менее селективным по мере роста таблицы. По природе индексирования в Firebird понятно, что такие индексы соответствия могут попросту убить эффективность запроса. Обсуждение этой проблемы см. в разд. "Тема оптимизации" главы 18.

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

Специальное отношение: пользовательская обработка ссылочной целостности

Этот раздел посвящен специальному виду отношений, системным соответствиям (lookup), которые обычно не поддерживаются в декларативной ссылочной целостности. Используемая здесь терминология соответствует требованиям подобного случая, поскольку установка полностью контролируемой пользователем ссылочной целостности является нецелесообразной для обычных отношений главная-подчиненная. Рис. 31.1 иллюстрирует эту ситуацию. Инициатор запроса, которым может быть любая таблица, имеет ключ соответствия, который указывает на одну, уникально определяемую ключом строку в таблице соответствия. Значение этой строки предоставляется таблице соответствия по запросу.

Рис. 31.1. Отношение-инициатор запроса - таблица соответствия

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

* Строка соответствия не может быть удалена, если на нее есть ссылки. Для этого нам нужен триггер BEFORE DELETE К таблице соответствия для проверки такой ситуации и при необходимости выдачи исключения и остановке операции.

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

* Нам может понадобиться правило, чтобы статичное значение никогда не менялось. В таблице налогов, например, один и тот же код налога (внешний) может быть связан с различными суммами и формулами в разные годы. Вероятно, только главному бухгалтеру будет позволено изменять строку соответствия.

* Триггер 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, которые нарушают целостность. Следовательно, вначале мы создадим эти исключения:

CREATE EXCEPTION NO_DELETE

'Can not delete row required by another table';

/* Нельзя удалять строку, нужную другой таблице */

CREATE EXCEPTION NOT_VALID_LOOKUP

'Not a valid lookup key';

/* Неверный ключ соответствия */

CREATE EXCEPTION NO_AUTHORITY

'You are not authorized to change this data';

/* Вы не можете изменять эти данные */

COMMIT;

Первый триггер выполняет проверку существования при попытке удалить строку соответствия:

SET TERM ^;

CREATE TRIGGER BD_LOOKUP FOR LOOKUP

ACTIVE BEFORE DELETE

AS

BEGIN

IF (EXISTS(

SELECT LOOKUP_ID FROM REQUESTOR

WHERE LOOKUP_ID = OLD.UQ_ID)) THEN

EXCEPTION NO_DELETE;

END ^

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

CREATE TRIGGER BA_REQUESTOR FOR REQUESTOR

ACTIVE BEFORE INSERT OR UPDATE

AS

BEGIN

IF (NEW.LOOKUP_ID IS NOT NULL

AND NOT EXISTS (

SELECT UQ_ID FROM LOOKUP

WHERE UQ_ID = NEW.LOOKUP_ID)) THEN

EXCEPTION NOT_VALID_LOOKUP;

END ^

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

CREATE TRIGGER BA_LOOKUP FOR LOOKUP

ACTIVE BEFORE UPDATE OR DELETE

AS

BEGIN