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?