refint

1. Функция check_primary_key() проверяет ссылающуюся таблицу.
   Для использования функции необходимо:
   • создать триггер BEFORE INSERT OR UPDATE с этой функцией для таблицы, ссылающейся на другую;
   • указать в аргументах триггера:
     • имена столбцов ссылающейся таблицы, образующих внешний ключ;
     • имя целевой таблицы;
     • имена столбцов в целевой таблице, образующих первичный/уникальный ключ;
   • для контроля нескольких внешних ключей необходимо создать триггер для каждой такой ссылки.
2. Функция check_foreign_key() проверяет целевую таблицу.
   Для использования функции необходимо:
   • создать триггер BEFORE DELETE OR UPDATE с этой функцией для таблицы, на которую ссылаются другие;
   • указать в аргументах триггера:
     • число ссылающихся таблиц, для которых функция должна выполнить проверки;
     • действие в случае обнаружения ссылающегося ключа:
       • cascade — удалить ссылающуюся строку;
       • restrict — прервать транзакцию;
       • setnull — установить в ссылающихся полях значения NULL;
     • имена столбцов целевой таблицы, образующих первичный/уникальный ключ;
     • имена таблиц и столбцов в количестве, задаваемом первым аргументом; поля первичных/уникальных столбцов должны иметь пометку NOT NULL и по ним должен быть создан уникальный индекс.

• refint (функции для реализации ссылочной целостности);
• autoinc (функции для автоувеличения полей);
• insert_username (отслеживание вносящего изменения пользователя);
• moddatetime (функции для отслеживания времени последнего изменения).

Использование модуля

1. Создать таблицу a с первичным ключом id:

   CREATE TABLE a (
     id int4 not null
   );
   CREATE UNIQUE INDEX ai ON a (id);
   

2. Создать таблицы b и c. Столбцы refb таблицы b и refc таблицы c являются внешними ключами, ссылающимися на id таблицы a:

   CREATE TABLE b (
    refb int4
   );
   CREATE INDEX bi ON b (refb);
   

   CREATE TABLE c (
    refc int4
   );
   CREATE INDEX ci ON c (refc);
   

3. Создать триггер для таблицы a:

   CREATE TRIGGER at BEFORE DELETE OR UPDATE ON a FOR EACH ROW
     EXECUTE PROCEDURE
       check_foreign_key (
         2,
         'cascade',
         'id',
         'b',
         'refb',
         'c',
         'refc'
       );
   

   где:
   • 2 - означает, что проверка должна быть выполнена для внешних ключей двух таблиц;
   • 'cascade' - определяет, что соответствующие ключи должны быть удалены;
   • id - имя столбца первичного ключа в инициируемой таблице a; можно использовать необходимое количество столбцов;
   • b - имя первой таблицы с внешними ключами;
   • refb - имя столбца внешнего ключа в таблице b; можно использовать необходимое количество столбцов, но количество ключевых столбцов в ссылках таблицы a должно быть таким же;
   • c - имя второй таблицы с внешними ключами;
   • refc - имя столбца внешнего ключа в таблице b.
   В итоге создана следующая структура таблицы a:

   Раскрыть type=sql
                      Table "ext.a"
    Column |  Type   | Collation | Nullable | Default
   --------+---------+-----------+----------+---------
    id     | integer |           | not null |
   Indexes:
       "ai" UNIQUE, btree (id)
   Triggers:
       at BEFORE DELETE OR UPDATE ON a FOR EACH ROW EXECUTE FUNCTION check_foreign_key('2', 'cascade', 'id', 'b', 'refb', 'c', 'refc')
   

4. Создать триггер для таблицы b:

   CREATE TRIGGER bt BEFORE INSERT OR UPDATE ON b FOR EACH ROW
     EXECUTE PROCEDURE
       check_primary_key (
         'refb',
         'a',
         'id'
       );
   

   где:
   • refb - имя столбца внешнего ключа в таблице с инициализацией (b); можно использовать необходимое количество столбцов, но количество ключевых столбцов в ссылках таблицы должно быть таким же;
   • A - имя таблицы, на которую указывает ссылка;
   • id - имя столбца первичного ключа в таблице, на которую указывает ссылка.
   В итоге создана следующая структура таблицы b:

   Раскрыть type=sql
                      Table "ext.b"
    Column |  Type   | Collation | Nullable | Default
   --------+---------+-----------+----------+---------
    refb   | integer |           |          |
   Indexes:
       "bi" btree (refb)
   Triggers:
       bt BEFORE INSERT OR UPDATE ON b FOR EACH ROW EXECUTE FUNCTION check_primary_key('refb', 'a', 'id')
   

5. Создать триггер для таблицы c:

   CREATE TRIGGER ct BEFORE INSERT OR UPDATE ON c FOR EACH ROW
     EXECUTE PROCEDURE
       check_primary_key (
         'refc',
         'a',
         'id'
       );
   

   где:
   • refc - имя столбца внешнего ключа в таблице с инициализацией (c); можно использовать необходимое количество столбцов, но количество ключевых столбцов в ссылках таблицы должно быть таким же;
   • A - имя таблицы, на которую указывает ссылка;
   • id - имя столбца первичного ключа в таблице, на которую указывает ссылка.
   В итоге создана следующая структура таблицы c:

   Раскрыть type=sql
                      Table "ext.c"
    Column |  Type   | Collation | Nullable | Default
   --------+---------+-----------+----------+---------
    refc   | integer |           |          |
   Indexes:
       "ci" btree (refc)
   Triggers:
       ct BEFORE INSERT OR UPDATE ON c FOR EACH ROW EXECUTE FUNCTION check_primary_key('refc', 'a', 'id')
   

6. Вставить данные и выполнить проверку:

   INSERT INTO a VALUES
     (10),
     (20),
     (30),
     (40),
     (50),
     (60);
   

7. Вставить в таблицу b недопустимые данные и получить ошибку:
   INSERT INTO b VALUES (1); -- недопустимая ссылка
   Пример ошибки выполнения запроса:

   Раскрыть type=sql
   ERROR:  tuple references non-existent key
   DETAIL:  Trigger "bt" found tuple referencing non-existent key in "a".
   

8. Вставить в таблицу b корректные данные с успешным завершением:

   INSERT INTO b VALUES
     (10),
     (30),
     (30); -- существующие ссылки
   

   Пример результата успешного выполнения запроса:
   INSERT 0 3
9. Вставить в таблицу c недопустимые данные и получить ошибку:
   INSERT INTO c VALUES (11); -- недопустимая ссылка
   Пример ошибки выполнения запроса:

   Раскрыть type=sql
   ERROR:  tuple references non-existent key
   DETAIL:  Trigger "ct" found tuple referencing non-existent key in "a".
   

10. Вставить в таблицу c корректные данные с успешным завершением:

INSERT INTO c VALUES
  (20),
  (20),
  (30); -- существующие ссылки

11. Извлечь данные из таблиц a, b, c:

SELECT * FROM a;
SELECT * FROM b;
SELECT * FROM c;

Раскрыть type=sql
 id
----
 10
 20
 30
 40
 50
 60
(6 rows)

 refb
------
   10
   30
   30
(3 rows)

 refc
------
   20
   20
   30
(3 rows)

12. Удалить строки:

DELETE FROM a WHERE id = 10;
DELETE FROM a WHERE id = 20;
DELETE FROM a WHERE id = 30;

Раскрыть type=sql
NOTICE:  at: 1 tuple(s) of b are deleted
NOTICE:  at: 0 tuple(s) of c are deleted
DELETE 1

NOTICE:  at: 0 tuple(s) of b are deleted
NOTICE:  at: 2 tuple(s) of c are deleted
DELETE 1

NOTICE:  at: 2 tuple(s) of b are deleted
NOTICE:  at: 1 tuple(s) of c are deleted
DELETE 1

13. Извлечь данные из таблиц a, b, c:

SELECT * FROM a;
SELECT * FROM b;
SELECT * FROM c;

Раскрыть type=sql
 id
----
 40
 50
 60
(3 rows)

Раскрыть type=sql
 refb
------
(0 rows)

Раскрыть type=sql
 refc
------
(0 rows)

Ссылки на документацию разработчика