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:
- Use
before update or delete
trigger: look at the graph and try to guess what happens after execution. It doesn't work, because it's executed immediately. - Use
after update or delete
constraintDEFERRABLE INITIALLY DEFERRED
trigger: How check count of CEOs ifstaff
is 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:
- define
after update or delete
triggers 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 onroles
andstaff
:
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;
- Define
DEFERRABLE INITIALLY DEFERRED
on 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.