I've a requirement to ensure that every company has at least one CEO.
Example database model with requirements db<>fiddle.
Basically, a CEO can't be removed from a company without naming another CEO. The CEO can't take another role in the company, when there is no other CEO. But it's OK to remove all 3 entities: company, staff, role.
I've tried following ideas:
before update or deletetrigger: look at the graph and try to guess what happens after execution. It doesn't work, because it's executed immediately.
after update or deleteconstraint
DEFERRABLE INITIALLY DEFERREDtrigger: How check count of CEOs if
staffis deleted too? Or how to allow, if company is deleted with staff and role? And it isn't easy to test with pgtap.
Any further ideas?
I'm asking not about creation, but about the constraint that a CEO can't be removed from company without naming another CEO. It's just most simple example of the difficulty with trigger and relations.
Found a workaround
See db<>fiddle. In the core following workaround seems to work:
after update or deletetriggers those checks the state after deletion. But don't check if related entity deleted to. Because relations are ignored, triggers are needed on whole chain except root. In the example on
CREATE FUNCTION roles_updates() RETURNS trigger AS $$ BEGIN IF -- check only if staff wasn't deleted too, ignore otherwise (select count(1) from staff where name = OLD.staffid) > 0 -- check if there is at least one ceo left and ( select count(1) from roles r join staff s on r.staffid = s.name where s.company in (select company from staff where name = OLD.staffid) and r.role = 'ceo') < 1 THEN RAISE EXCEPTION 'roles: company without ceo isn''t allowed'; END IF; RETURN OLD; END; $$ LANGUAGE plpgsql;
DEFERRABLE INITIALLY DEFERREDon relations. This allows check for foreign keys at the end of transaction. Therefore I can use:
begin; delete from company where name = 'amd'; delete from staff where name = 'lisa'; delete from roles where id = 1; rollback;
instead to put it into one statement/common table expression like:
with DeletedCompany as ( delete from company where name = 'amd' ), DeletedStaff as ( delete from staff where name = 'lisa' ) delete from roles where id = 1;
There is still a non working use case:
begin; delete from roles where id = 1; delete from staff where name = 'lisa'; delete from company where name = 'amd'; rollback;
I think it can be fixed with usage of constraint trigger, which is executed at the end of transaction. But because it is hard to test, I prefer above workaround.
EDIT 15.06.21: as @DanielVérité pointed out, this only works in serialization isolation mode without anomalies. Read more about this topic in docs and drawbacks, especially about handling on client side.