refint. Функции для реализации ссылочной целостности#

В исходном дистрибутиве установлено по умолчанию: нет.

Связанные компоненты: отсутствуют.

Схема размещения: ext.

Функции для реализации ссылочной целостности применяются для проверки ограничений внешних ключей:

Функция check_primary_key() проверяет ссылающуюся таблицу.

Для использования функции необходимо:
- создать триггер BEFORE INSERT OR UPDATE с этой функцией для таблицы, ссылающейся на другую;
- указать в аргументах триггера:
  - имена столбцов ссылающейся таблицы, образующих внешний ключ;
  - имя целевой таблицы;
  - имена столбцов в целевой таблице, образующих первичный/уникальный ключ;
- для контроля нескольких внешних ключей необходимо создать триггер для каждой такой ссылки.
Функция check_foreign_key() проверяет целевую таблицу.

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

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

Примечание:

Функция refint входит в модуль spi, который предоставляет несколько рабочих примеров использования «Интерфейса программирования сервера» (Server Programming Interface, SPI) и триггеров. Эти функции полезны как сами по себе и как заготовки, которые можно приспособить под собственные нужды.

Каждая группа функций представлена в виде отдельно устанавливаемого расширения:

refint. Функции для реализации ссылочной целостности;

autoinc. Функции для автоувеличения полей;

insert_username. Отслеживание вносящего изменения пользователя;

moddatetime. Функции для отслеживания времени последнего изменения.

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

Доработка#

Не проводилась.

Установка#

При наличии прав администратора СУБД включение модуля выполняется запросом:

CREATE EXTENSION refint SCHEMA ext;

Настройка#

Не требуется.

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

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

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

Создать таблицы 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);
```
Создать триггер для таблицы 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:
```
                   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')
```
Создать триггер для таблицы 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:
```
                   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')
```
Создать триггер для таблицы 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:
```
                   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')
```

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

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

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

INSERT INTO b VALUES (1); -- недопустимая ссылка

Пример ошибки выполнения запроса:

ERROR:  tuple references non-existent key
DETAIL:  Trigger "bt" found tuple referencing non-existent key in "a".

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

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

Пример результата успешного выполнения запроса:

INSERT 0 3

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

INSERT INTO c VALUES (11); -- недопустимая ссылка

Пример результата выполнения запроса:

ERROR:  tuple references non-existent key
DETAIL:  Trigger "ct" found tuple referencing non-existent key in "a".

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

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

Пример результата успешного выполнения запроса:

INSERT 0 3

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

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

Пример результата выполнения запроса:

 id 
----
 10
 20
 30
 40
 50
 60
(6 rows)

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

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

Удаление строк:

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

Пример результата выполнения запроса:

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

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

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

Пример результата выполнения запроса:

 id 
----
 40
 50
 60
(3 rows)

 refb 
------
(0 rows)

 refc 
------
(0 rows)

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

Исходная документация PosgreSQL по модуль refint: https://www.postgresql.org/docs/15/contrib-spi.html#id-1.11.7.50.5