Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table company (
  name text not null,
  primary key (name)
);

create table staff (
  name text not null,
  -- DEFERRABLE INITIALLY DEFERRED allows out-of-order deletion
  company text not null references company (name) DEFERRABLE INITIALLY DEFERRED,
  primary key (name)
);

create table roles (
  id int NOT NULL,
  role text not null,
  staffid text not null references staff (name) DEFERRABLE INITIALLY DEFERRED,
  primary key (id)
);

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;

CREATE TRIGGER roles_updates
AFTER UPDATE OR DELETE ON roles
FOR EACH ROW
EXECUTE FUNCTION roles_updates();

CREATE FUNCTION staff_updates() RETURNS trigger AS $$
BEGIN
  IF
    -- check only if company wasn't deleted too, ignore otherwise
    (select count(1) from company where name = OLD.company) > 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 = OLD.company
          and r.role = 'ceo') < 1
  THEN
    RAISE EXCEPTION 'staff: company without ceo isn''t allowed';
  END IF;

  RETURN OLD;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER staff_updates
AFTER UPDATE OR DELETE ON staff
FOR EACH ROW
EXECUTE FUNCTION staff_updates();

insert into company values ('amd');
insert into company values ('intel');
insert into staff values ('lisa', 'amd');
insert into roles values (1, 'ceo', 'lisa');

create view ceo as (
  select s.name as ceo
  from roles r
  join staff s on r.staffid = s.name
  where r.role = 'ceo'
);
1 rows affected

1 rows affected

1 rows affected

1 rows affected

-- ok: delete whole graph, works without DEFERRABLE INITIALLY DEFERRED
begin;
with DeletedCompany as (
  delete from company where name = 'amd'
),
DeletedStaff as (
  delete from staff where name = 'lisa'
)
delete from roles where id = 1;
select * from company;
select * from staff;
select * from roles;
rollback;
1 rows affected



name


intel


-- ok: delete whole graph, works only with DEFERRABLE INITIALLY DEFERRED
begin;
delete from company where name = 'amd';
delete from staff where name = 'lisa';
delete from roles where id = 1;

select * from company;
select * from staff;
select * from roles;
rollback;
1 rows affected

1 rows affected

1 rows affected



name


intel


-- should be ok: delete whole graph, probably needs trigger
-- constraint instead of "normal" trigger

-- begin;
-- delete from roles where id = 1;
-- delete from staff where name = 'lisa';
-- delete from company where name = 'amd';
-- rollback;
-- ok: another staff member took the ownership
begin;
insert into staff values ('larissa', 'amd');
insert into roles values (2, 'ceo', 'larissa');
select * from company;
select * from staff;
select * from roles;

-- ceos' before deletion
select * from ceo;

delete from roles where id = 1;

-- ceos' after deletion
select * from ceo;

rollback;
1 rows affected

1 rows affected



name


amd

intel




name
company


lisa
amd

larissa
amd




id
role
staffid


1
ceo
lisa

2
ceo
larissa




ceo


lisa

larissa


1 rows affected



ceo


larissa


-- ok: another staff member took the ownership
begin;
insert into staff values ('larissa', 'amd');
insert into roles values (2, 'ceo', 'larissa');
select * from company;
select * from staff;
select * from roles;

-- ceos' before deletion
select * from ceo;

delete from roles where id = 1;
delete from staff where name = 'lisa';

-- ceos' after deletion
select * from ceo;

rollback;
1 rows affected

1 rows affected



name


amd

intel




name
company


lisa
amd

larissa
amd




id
role
staffid


1
ceo
lisa

2
ceo
larissa




ceo


lisa

larissa


1 rows affected

1 rows affected



ceo


larissa


-- fails: company without ceo isn't allowed
-- begin;
-- delete from roles where id = 1;
-- rollback;
-- fails: company without ceo isn't allowed
-- begin;
-- update roles set role = 'cto' where id = 1;
-- rollback;
-- fails: company without ceo isn't allowed
-- begin;
-- update staff set company = 'intel' where name = 'lisa';
-- rollback;
-- fails: company without staff isn't allowed
begin;
delete from roles where id = 1;
delete from staff where name = 'lisa';
rollback;
ERROR:  roles: company without ceo isn't allowed
CONTEXT:  PL/pgSQL function roles_updates() line 15 at RAISE