Просмотр исходного текста страницы Замена FOREIGN KEY (постановка)

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

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

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

Рассмотрим замену ограничения 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

[[Category:Постановка-Сделано]]