Enforce that every company has at least one CEO

Question

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:

1. 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.
2. Use after update or delete constraint DEFERRABLE INITIALLY DEFERRED trigger: How check count of CEOs if staff 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:

1. 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 on roles and staff:

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;

2. 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.

Answer 1

The main problem with your trigger is that it's oblivious to concurrent changes. You requested an example in the comments so here's a simple one.

Starting from the dbfiddle with 'lisa' being CEO of 'amd', let's add a second CEO:

insert into staff values (alice', 'amd');
insert into roles values (2, 'ceo', 'alice');

tst=> select * from roles;
 id | role | staffid 
----+------+---------
  1 | ceo  | lisa
  2 | ceo  | alice

Now remove the two CEOs in two distinct concurrent transactions:

Tx1> begin;
BEGIN

Tx1*> delete from roles where staffid='lisa';
DELETE 1

Tx2> begin;
BEGIN

Tx2*> delete from roles where staffid='alice';
DELETE 1

Tx2*> commit;
COMMIT

Tx1*> commit;
COMMIT

Tx1> select * from roles ;
 id | role | staffid 
----+------+---------
(0 rows)

Result: no error was raised, and 'amd' has no CEO left.

The check implemented in the trigger is quietly passed, because when Tx2 runs the trigger, as far as it's concerned, the role with 'lisa' is still there.

So how to avoid that? Generally speaking, you can lock objects or rows before doing anything with them (pessimistic locking preventing concurrency), or use serializable transactions (optimistic locking) with a retry-on-error logic. But a trigger is not a place to do that.

Answer 2

One way to handle this is to create a foreign key from company to roles that always points to one of the CEOs:

/* redundant, but needed as destination of the foreign key */
ALTER TABLE roles ADD UNIQUE (role, id);

/* also redundant */
ALTER TABLE company ADD ceo_role text DEFAULT 'ceo' NOT NULL;
ALTER TABLE company ADD CHECK (ceo_role = 'ceo');
ALTER TABLE company ADD ceo_id integer;
UPDATE company SET ceo_id = roles.id
   FROM staff JOIN roles ON roles.staffid = staff.name
   WHERE staff.company = company.name
     AND roles.role = 'ceo'
     /* only take the CEO with the least roles.id */
     AND NOT EXISTS (SELECT 1
                     FROM staff AS s2 JOIN roles AS r2 ON r2.staffid = d2.name
                     WHERE s2.company = company.name
                       AND r2.role = 'ceo'
                       AND r2.id < roles.id)

ALTER TABLE company ADD CONSTRAINT has_at_least_one_ceo
   FOREIGN KEY (ceo_role, ceo_id) REFERENCES roles (role, id)
   DEFERRABLE INITIALLY DEFERRED;

I made the foreign key deferrable so that you can add a company and its first CEO in a single transaction – you just have to add the company first.

The drawbacks of this solution are

1. the redundant data

2. the complicated procedure needed to remove a CEO

On the positive side, you don't have to worry about concurrency in a trigger function.