Книга: Firebird РУКОВОДСТВО РАЗРАБОТЧИКА БАЗ ДАННЫХ

Поддержка ссылочной целостности

Поддержка ссылочной целостности

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

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

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

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

Это полностью допустимый и стандартный способ использования отношений - что мы можем сделать без него? Однако существует тенденция использования небольшого количества значений ключей соответствия в большой динамичной пользовательской таблице. Такие большие таблицы часто содержат немалое количество подобных ключей соответствия в виде внешних ключей, а с ними и множество автоматических нехороших индексов, которые не могут быть удалены. Результатом небольшого количества значений в большом индексе может стать увеличение размера индекса, который становится все менее и менее селективным по мере роста таблицы. По природе индексирования в 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

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;

BEGIN

IF (INSERTING AND NEW.LOOKUP_ID IS NOT NULL) THEN

NEED_CHECK = 1;

IF (UPDATING) THEN

IF (

(OLD.LOOKUP_ID IS NULL

AND NEW.LOOKUP_ID IS NOT NULL)

OR (OLD.LOOKUP_ID IS NOT NULL

AND NEW.LOOKUP_ID <> OLD.LOOKUP_ID)) THEN

NEED_CHECK = 1;

IF (NEED_CHECK = 1) THEN

BEGIN

SELECT L1.UQ_ID FROM LOOKUP L1

WHERE L1.START_DATE <= CAST(NEW.TRANSAC_DATE AS DATE)

AND L1.END_DATE >= CAST(NEW.TRANSAC_DATE AS DATE)

AND L1.VALUE2 = (SELECT L2.VALUE2 FROM LOOKUP L2

WHERE L2.UQ_ID = NEW.LOOKUP_ID)

INTO :LOOKUP_NUM;

NEW.LOOKUP_ID = LOOKUP_NUM;

END

END ^

COMMIT ^

SET TERM ;^

Оглавление книги