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

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

IF (CURRENT_USER <> 'CHIEFACCT') THEN

EXCEPTION NO_AUTHORITY;

END ^

Этот триггер будет проверять входной код соответствия, чтобы убедиться, что он правилен для периода транзакции, и будет корректировать его при необходимости:

CREATE TRIGGER BA_REQUESTORl FOR REQUESTOR

ACTIVE BEFORE INSERT OR UPDATE POSITION 1

AS

DECLARE VARIABLE LOOKUP_NUM SMALLINT;

DECLARE VARIABLE NEED_CHECK SMALLINT = 0;