7👍
In Postgres, there are two types of constraints (other than things like unique and foreign key constraints), CHECK CONSTRAINTS and EXCLUDE constraints.
Check constraints can only apply to a single row.
Exclusion constraints can only apply to a single table.
You will not be able to use either of these to enforce the constraint you want, which crosses table boundaries to ensure consistency.
What you could use instead are trigger-based constraints, that can perform other queries in order to validate the data.
For instance, you could have a BEFORE INSERT OR UPDATE trigger on the various tables that checks the users match. I have some similar code that runs on same self-relation tree code that ensures a parent and child both have the same “category” as one another.
In this case, it’s going to be a bit trickier, because you would need some mechanism of preventing the check until all tables involved have been updated.