Пример реализации сложных ограничений целостности для обеспечения согласованности данных.
Имеем дело с проводками в учетной системе. Каждая проводка имеет одну строку заголовка в таблице headers и несколько строк с дебетуемыми и кредитуемыми счетами в таблице lines.
Очевидное требование к данным, чтобы сумма всех дебетуемых и кредитуемых счетов у одной проводки была одинаковой. Также не должно быть строк в headers без соответствующих строк в lines.
Пока транзакция не завершена, мы можем добавлять/изменять/удалять любое количество записей в lines для одной проводки. Но в момент завершения транзакции нужно гарантировать равенство сумм по дебету и кредиту проводки.
Решить такую задачу на уровне базы данных можно следующим образом.
Понадобится строчный триггер на lines, который автоматически поддерживает сумму всех дебетуемых и кредитуемых счетов в дополнительных столбцах в headers: amount_dr, amount_cr.
Триггер гарантирует, что после каждой команды insert/update/delete в lines, в таблице headers обновляются итоговые суммы по дебету и кредиту.
Теперь напрашивается сделать отложенное (deferred) ограничение check в headers с условием amount_dr = amount_cr.
И всё. Пока транзакция не завершена, это условие не будет проверяться, но commit зафиксирует только состояние равенства.
Однако PostgreSQL разрешает сделать отложенными только ограничения PRIMARY KEY, UNIQUE, FOREIGN KEY, EXCLUDE. А ограничения CHECK и NOT NULL нет. В этом есть своя логика, но сейчас она мешает.
Поскольку нам нужно именно отложенное ограничение, то воспользуемся FOREIGN KEY вместо CHECK.
Как? Придется добавить в headers еще один фиктивный столбец, в который тот же триггер на lines будет записывать true или false, в зависимости от того, являются ли текущие итоги по дебету и кредиту сбалансированными. И создать внешний ключ на этот столбец, который ссылается на фиктивную таблицу с одним столбцом и одной строкой со значение true.
И этот внешний ключ будет отложенным.
Впрочем, вот рабочий протитип:
create table dummy (
pk boolean primary key
);
insert into dummy values (true);
create table headers (
header_id int primary key,
accounting_date date default now()::date,
amount_dr numeric(20,2) not null default 0,
amount_cr numeric(20,2) not null default 0,
is_balance boolean not null default false,
foreign key (is_balance) references dummy (pk)
deferrable initially deferred
);
create table lines (
header_id int,
line_id int,
account text not null,
amount_dr numeric(20,2) not null,
amount_cr numeric(20,2) not null,
primary key (header_id, line_id),
foreign key (header_id) references headers (header_id) on delete cascade,
/* счет или дебетуется, или кредитуется */
check ((amount_dr <> 0 and amount_cr = 0)
or (amount_dr = 0 and amount_cr <> 0))
);
create function check_balance() returns trigger
as $$
<<local>>
declare
change_dr lines.amount_dr%type;
change_cr lines.amount_cr%type;
header_id lines.header_id%type;
begin
if TG_OP = 'DELETE' then
change_dr := -1 * old.amount_dr;
change_cr := -1 * old.amount_cr;
local.header_id := old.header_id;
elsif TG_OP = 'UPDATE' then
change_dr := new.amount_dr - old.amount_dr;
change_cr := new.amount_cr - old.amount_cr;
local.header_id := new.header_id;
elsif TG_OP = 'INSERT' then
change_dr := new.amount_dr;
change_cr := new.amount_cr;
local.header_id := new.header_id;
end if;
update headers h
set amount_dr = amount_dr + change_dr,
amount_cr = amount_cr + change_cr,
is_balance = (amount_dr + change_dr = amount_cr + change_cr) and
(amount_dr + change_dr) <> 0
where h.header_id = local.header_id;
return new;
end;
$$ language plpgsql;
create trigger line_change
after insert or delete or update of amount_dr, amount_cr
on lines
for each row
execute procedure check_balance();
Проверяем.
Сценарий 1. Нельзя зафиксировать заголовок проводки без строк:
demo=# begin;
BEGIN
demo=# insert into headers (header_id) values (1);
INSERT 0 1
demo=# select * from headers;
header_id | accounting_date | amount_dr | amount_cr | is_balance
-----------+-----------------+-----------+-----------+------------
1 | 2017-03-02 | 0.00 | 0.00 | f
(1 row)
demo=# commit;
ERROR: insert or update on table "headers" violates foreign key constraint "headers_is_balance_fkey"
DETAIL: Key (is_balance)=(f) is not present in table "dummy".
Сценарий 2. Если проводка не сбалансирована:
demo=# begin;
BEGIN
demo=# insert into headers (header_id) values (1);
INSERT 0 1
demo=# insert into lines values (1,1,'10', 1000, 0);
INSERT 0 1
demo=# insert into lines values (1,2,'60', 0, 1180);
INSERT 0 1
пока транзакция незавершена - всё ок:
demo=# select * from headers;
header_id | accounting_date | amount_dr | amount_cr | is_balance
-----------+-----------------+-----------+-----------+------------
1 | 2017-03-02 | 1000.00 | 1180.00 | f
(1 row)
пробуем завершить - получаем ошибку:
demo=# select * from headers;
header_id | accounting_date | amount_dr | amount_cr | is_balance
-----------+-----------------+-----------+-----------+------------
1 | 2017-03-02 | 1000.00 | 1180.00 | f
(1 row)
пробуем завершить - получаем ошибку:
demo=# commit;
ERROR: insert or update on table "headers" violates foreign key constraint "headers_is_balance_fkey"
DETAIL: Key (is_balance)=(f) is not present in table "dummy".
Сценарий 3. Сбалансированная проводка
demo=# begin;
BEGIN
demo=# insert into headers (header_id) values (1);
INSERT 0 1
demo=# insert into lines values (1,1,'10', 1000, 0);
INSERT 0 1
demo=# insert into lines values (1,2,'60', 0, 1180);
INSERT 0 1
demo=# insert into lines values (1,3,'19', 180, 0);
INSERT 0 1
demo=# commit;
COMMIT
проверим, что всё нормально:
demo=# select * from headers;
header_id | accounting_date | amount_dr | amount_cr | is_balance
-----------+-----------------+-----------+-----------+------------
1 | 2017-03-02 | 1180.00 | 1180.00 | t
(1 row)
demo=# select * from lines;
header_id | line_id | account | amount_dr | amount_cr
-----------+---------+---------+-----------+-----------
1 | 1 | 10 | 1000.00 | 0.00
1 | 2 | 60 | 0.00 | 1180.00
1 | 3 | 19 | 180.00 | 0.00
(3 rows)
Конечно, такое решение не лишено недостатков в виде фиктивной однострочной таблицы, дополнительных столбцов в headers и необходимости их поддержки. Но с другой стороны, можно не беспокоиться, что прикладной код нарушит согласованность данных.
