For context, this sample schema is for a university environment.

I have this employee_position table:

DROP TABLE IF EXISTS "public"."employee_position";
CREATE TABLE "public"."employee_position" (
  "employee_positionID" int4 NOT NULL DEFAULT nextval('"employee_position_employee_positionID_seq"'::regclass),
  "employeeID" int4 NOT NULL,
  "positionID" int4 NOT NULL,
  "scheduleID" int4 NOT NULL,
  "departmentID" int4 NOT NULL
);

And this position_schedule table wrapping each position with the schedule(s) that comes with it:

DROP TABLE IF EXISTS "public"."position_schedule";
CREATE TABLE "public"."position_schedule" (
  "position_scheduleID" int4 NOT NULL DEFAULT nextval('"position_schedule_position_scheduleID_seq"'::regclass),
  "positionID" int4 NOT NULL,
  "scheduleID" int4 NOT NULL,
);

Is there a way – other than using trigger functions – to ensure that when an employee is registered in the employee_position table, the corresponding schedule(s) are also correctly registered?

For example, a developer position can’t work from midnight to 6 AM, or a security guard can’t work remotely, etc.

I thought about using check constraints with something like this:

ALTER TABLE employee_position
ADD CONSTRAINT fk_employee_position_position_schedule
FOREIGN KEY (positionid, scheduleid)
REFERENCES position_schedule (positionid, scheduleid);

I’m not sure if this is a better practice than triggers though (or if it’s even correct in any way) noting that I’m still learning pgSQL.

Any hint/advice would help!