Clarification
The formulation of this requirement leaves room for interpretation:
"where user_roles.role_name contains an employee role name."
My interpretation:
"with an entry in user_roles that has role_name = 'employee'."
The problem
FOREIGN KEY and CHECK constraints are proven, air-tight tools to enforce relational integrity. Triggers are powerful, versatile features but more sophisticated, less strict, and with more room for design errors and corner cases.
A FK constraint seems impossible for your case at first: it requires a PRIMARY KEY or UNIQUE constraint to reference - neither allows NULL values. There are no partial FK constraints. The only escape from strict referential integrity are null values in referencing columns - with default MATCH SIMPLE behavior of FK constraints. The manual:
MATCH SIMPLE allows any of the foreign key columns to be null; if any
of them are null, the row is not required to have a match in the referenced table.
See:
The workaround is to introduce a boolean flag is_employee to mark employees on both sides, defined NOT NULL in users, but allowed to be NULL in user_roles:
Solution
This enforces your requirements exactly, while keeping noise and overhead to a minimum:
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)
);
That's all.
Optional triggers for convenience
The following triggers are optional, but recommended for convenience to set the added tags is_employee automatically and you don't have to do anything extra:
-- 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();
No-nonsense, optimized and only called when needed.
(Before Postgres 11, use the (misleading) key word PROCEDURE. instead,)
fiddle
Old sqlfiddle. (Works with ancient Postgres 9.1, too.)
Major points
Now, if we want to set user_roles.role_name = 'employee', then there has to be a matching user.employee_nr first.
You can still add an employee_nr to any user, and you can (then) still tag any user_roles with is_employee, regardless of the actual role_name. Easy to disallow if you need to, but this implementation does not introduce any more restrictions than required.
users.is_employee can only be true or false and is forced to reflect the existence of an employee_nr by the CHECK constraint. The trigger keeps the column in sync automatically. You could allow false additionally for other purposes with only minor updates to the design.
The rules for user_roles.is_employee are slightly different: it must be true if role_name = 'employee'. Enforced by a CHECK constraint and set automatically by the trigger again. But it's allowed to change role_name to something else and still keep is_employee. Nobody said a user with an employee_nr is required to have an according entry in user_roles, just the other way round! Again, easy to enforce additionally if needed.
If there are other triggers that could interfere, consider:
But we need not worry that rules might be violated by the triggers. The requested rules are enforced with CHECK and FK constraints, which allow no exceptions.
Aside: I put the column is_employee first in the constraint UNIQUE (is_employee, users_id) for a reason. users_id is already covered in the PK, so it can take second place here:
