Is there a way to enforce database logic that spans tables? As a very VERY simple super-cut-down example, imagine a two-table invoice system:
create table accounts (id serial primary key, balance integer not null default 0);
create table invoices (id serial primary key, acct integer references accounts, total integer not null);
Raising an invoice consists of inserting a row into the invoices table, and updating the accounts table with the new balance. (Yes, I'm aware there are normalization issues here, but there are other reasons for wanting the balance to be in the primary table. Also, this is hugely oversimplified.)
There is an invariant here: For any given account, (select balance from accounts where id=N)==(select sum(total) from invoices where acct=N) - or, putting it another way, select acct,sum(total) from invoices group by acct should be the same as select id,balance from accounts (although the latter will have zeroes for those with no invoices).
Is there a way to enforce this in PostgreSQL? I'd rather not have to trust the client code.
I think you need also a trigger. Catch the Before Insert or After Insert event, and Before Update or After Update event. (I catched ever before, but for portability reason now I catch the after) On Insert and update, you updates also all the columns you need. Here how you build a trigger: https://stackoverflow.com/questions/30824126/postgresql-how-to-autofill-column-based-on-other-previous-values/30824693?noredirect=1#comment49795847_30824693