two-table-over  functional relationship: maintaining referential integrity I have three tables, the last two having a many to one relationship with the first, written here in SQL:

create table study (
    id bigint primary key,
    name text
);

create table treatment (
    id bigint primary key,
    name text,
    study_id bigint references study
);

create table visit (
    id bigint primary key,
    name text,
    study_id bigint references study
);


These are brought together into a session which ties a person assigned a treatment on some visit in some study like so:

create table session (
    id bigint primary key,
    person_id bigint references person,
    study_id bigint references study,
    visit_id bigint references visit,
    treatment_id bigint references treatment
);


What is the best way to make sure that for any row in the session table, the study_id, the visit_id, and the treatment_id all belong to the same study? I am working in a Postgres 9.x database. Check constraints can't refer to other tables, so maybe a trigger before insert and update?
You add a (redundant) unique constraint on (study_id, id) on both the treatment and visit tables. Then let the foreign keys from session refer to these unique constraints by including study_id into the respective foreign keys.

Then your condition will automatically be satisfied.

The unique constraints are not quite as superfluous as they seem, because the foreign key constraint on study_id should be indexed anyway.

To put it into SQL:

ALTER TABLE treatment
   ADD UNIQUE (study_id, id);

ALTER TABLE visit
   ADD UNIQUE (study_id, id);

ALTER TABLE session
   DROP CONSTRAINT session_treatment_id_fkey,
   DROP CONSTRAINT session_visit_id_fkey,
   ADD FOREIGN KEY (study_id, treatment_id) REFERENCES treatment (study_id, id),
   ADD FOREIGN KEY (study_id, visit_id) REFERENCES visit (study_id, id);