Замена FOREIGN KEY (постановка)

Создаем утилиту (компоненту) которая делает следующее:

1. Составляет список внешних ключей в базе данных
2. Отображает список пользователю. Подсказывает какие ключи имеет смысл удалить, на основании селективности и общем количестве записей в таблице
3. Пользователь выбирает из списка внешние ключи для удаления и запускает процесс
4. Информация о таких ключах переносится в специально созданную таблицу со структурой аналогичной существующей RDB$ таблице
5. FOREIGN KEY удаляется
6. Создаются триггеры на обеих таблицах, участвующих в отношении, для контролирования ссылочной целостности и выполнения правил ON UPDATE, ON DELETE
7. Утилита может работать и в обратную сторону, удаляя триггеры и восстанавливая FOREIGN KEY
8. Программный код, который зависит от информации о внешних ключах, теперь должен обращаться и к RDB$ таблице и к нашей таблице
9. Программный код, который обрабатывает стандартную ошибку -- нарушение ссылочной целостности -- теперь должен обрабатывать и наше генерируемое исключение

Проверка в триггере

Рассмотрим замену ограничения FOREIGN KEY на таком примере:

 ALTER TABLE gd_document ADD CONSTRAINT gd_fk_document_creatorkey
   FOREIGN KEY (creatorkey) REFERENCES gd_people(contactkey)
   ON UPDATE CASCADE;

Создадим на таблице GD_DOCUMENT триггер:

 CREATE TRIGGER gd_aiu_document FOR gd_document
   AFTER INSERT OR UPDATE
   POSITION 0
 AS BEGIN
   IF (NOT EXISTS(SELECT contactkey FROM gd_people WHERE contactkey = NEW.creatorkey)) THEN
     EXCEPTION gd_e_fk_violation 'Поле creatorkey в таблице gd_document содержит ссылку на несуществующую запись.' ;
 END

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

   IF ((NEW.creatorkey NOT IS NULL) AND (NOT EXISTS(SELECT contactkey FROM gd_people WHERE contactkey = NEW.creatorkey))) THEN
     EXCEPTION gd_e_fk_violation 'Поле creatorkey в таблице gd_document содержит ссылку на несуществующую запись.' ;

Для реализации правила ON UPDATE CASCADE создадим триггер на таблице GD_PEOPLE:

 CREATE TRIGGER gd_au_people FOR gd_people
   AFTER UPDATE
   POSITION 0
 AS BEGIN
   IF (NEW.contactkey <> OLD.contactkey) THEN
   BEGIN
     UPDATE gd_document SET creatorkey = NEW.contactkey WHERE creatorkey = OLD.contactkey;
   END
 END

Реализуем правило ON DELETE NO ACTION:

 CREATE TRIGGER gd_ad_people FOR gd_people
   AFTER DELETE
   POSITION 0
 AS BEGIN
   IF (EXISTS (SELECT creatorkey FROM gd_document WHERE creatorkey = OLD.contactkey)) THEN
   BEGIN
     EXCEPTION gd_e_fk_violation 'Попытка удалить запись на которую существуют ссылки в таблице gd_document, поле creatorkey.' ;
   END
 END

Если бы правило было задано как ON DELETE CASCADE:

 CREATE TRIGGER gd_ad_people FOR gd_people
   AFTER DELETE
   POSITION 0
 AS BEGIN
   DELETE FROM gd_document WHERE creatorkey = OLD.contactkey;
 END

Массив констант

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

 CREATE TRIGGER gd_aiu_document FOR gd_document
   AFTER INSERT OR UPDATE
   POSITION 0
 AS BEGIN
   IF (NEW.creatorkey NOT IS NULL) THEN
   BEGIN
     IF (NOT NEW.creatorkey IN (id1, id2, id3, id4, ....)) THEN
     BEGIN
       IF (NOT EXISTS(SELECT contactkey FROM gd_people WHERE contactkey = NEW.creatorkey)) THEN
         EXCEPTION gd_e_fk_violation 'Поле creatorkey в таблице gd_document содержит ссылку на несуществующую запись.' ;
     END
   END
 END

Ручная обработка

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

 CREATE TRIGGER gd_aiu_document FOR gd_document
   AFTER INSERT OR UPDATE
   POSITION 0
 AS BEGIN
   IF (NEW.creatorkey NOT IS NULL) THEN
   BEGIN
     IF (NOT EXISTS(SELECT contactkey FROM gd_user WHERE contactkey = NEW.creatorkey)) THEN
     BEGIN
       IF (NOT EXISTS(SELECT contactkey FROM gd_people WHERE contactkey = NEW.creatorkey)) THEN
         EXCEPTION gd_e_fk_violation 'Поле creatorkey в таблице gd_document содержит ссылку на несуществующую запись.' ;
     END
   END
 END