Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE users (
  users_id    serial PRIMARY KEY
, employee_nr int
, is_employee bool NOT NULL DEFAULT false
, CONSTRAINT role_employee CHECK (employee_nr IS NOT NULL = is_employee)  
, UNIQUE (is_employee, users_id)  -- required for FK (otherwise redundant)
);

CREATE TABLE user_roles (
  user_roles_id serial PRIMARY KEY
, users_id     int NOT NULL REFERENCES users
, role_name    text NOT NULL
, is_employee  bool CHECK(is_employee)
, CONSTRAINT role_employee CHECK (role_name <> 'employee' OR is_employee IS TRUE)
, CONSTRAINT role_employee_requires_employee_nr_fk
  FOREIGN KEY (is_employee, users_id) REFERENCES users(is_employee, users_id)
);

CREATE TABLE



CREATE TABLE


-- Both triggers are optional convenience features!
-- users
CREATE OR REPLACE FUNCTION trg_users_insup_bef()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   NEW.is_employee := (NEW.employee_nr IS NOT NULL);
   RETURN NEW;
END
$func$;

CREATE TRIGGER insup_bef
BEFORE INSERT OR UPDATE OF employee_nr ON users
FOR EACH ROW
EXECUTE FUNCTION trg_users_insup_bef();


-- user_roles
CREATE OR REPLACE FUNCTION trg_user_roles_insup_bef()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   NEW.is_employee = true;
   RETURN NEW;
END
$func$;

CREATE TRIGGER insup_bef
BEFORE INSERT OR UPDATE OF role_name ON user_roles
FOR EACH ROW
WHEN (NEW.role_name = 'employee')
EXECUTE FUNCTION trg_user_roles_insup_bef();

CREATE FUNCTION



CREATE TRIGGER



CREATE FUNCTION



CREATE TRIGGER


-- sample data
INSERT INTO users(users_id, employee_nr) VALUES
  (default, NULL)
, (default, NULL)
, (default, 123)  -- this one
RETURNING *;

INSERT INTO user_roles(users_id, role_name) VALUES
  (1, 'killer')
, (2, 'burgler')
, (3, 'thief')
, (3, 'employee')
RETURNING *;


users_id
employee_nr
is_employee


1
null
f

2
null
f

3
123
t



INSERT 0 3




user_roles_id
users_id
role_name
is_employee


1
1
killer
null

2
2
burgler
null

3
3
thief
null

4
3
employee
t



INSERT 0 4


-- Violates FK and fails!
INSERT INTO user_roles(users_id, role_name) VALUES (2, 'employee');
UPDATE users SET employee_nr = NULL WHERE users_id = 3;

ERROR:  insert or update on table "user_roles" violates foreign key constraint "role_employee_requires_employee_nr_fk"
DETAIL:  Key (is_employee, users_id)=(t, 2) is not present in table "users".


DELETE FROM user_roles WHERE user_roles_id = 4;
-- now we can:
UPDATE users SET employee_nr = NULL WHERE users_id = 3;

UPDATE users SET employee_nr = 436 WHERE users_id = 2;
-- now it works:
INSERT INTO user_roles(users_id, role_name) VALUES (2, 'employee');

DELETE 1



UPDATE 1



UPDATE 1



INSERT 0 1


TABLE users;
TABLE user_roles;


users_id
employee_nr
is_employee


1
null
f

3
null
f

2
436
t



SELECT 3




user_roles_id
users_id
role_name
is_employee


1
1
killer
null

2
2
burgler
null

3
3
thief
null

6
2
employee
t



SELECT 4


-- cannot take that back now:
UPDATE users SET employee_nr = NULL WHERE users_id = 2;

ERROR:  update or delete on table "users" violates foreign key constraint "role_employee_requires_employee_nr_fk" on table "user_roles"
DETAIL:  Key (is_employee, users_id)=(t, 2) is still referenced from table "user_roles".