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