On Tue, 2021-08-17 at 10:45 -0700, Paul Martinez wrote: > On Tue, Aug 17, 2021 at 8:41 AM Jack Christensen <j...@jncsoftware.com> wrote: > > The only way to ensure a user can only be a member of a group in the same > > tenant is to user_group_memberships.tenant_id be part of the foreign key. > > And > > that will only work with a unique key on id and tenant_id in both users and > > user_groups. It's a bit inelegant to create multiple extra indexes to ensure > > consistency when existing indexes are enough to ensure uniqueness. > > You could accomplish this by using composite primary keys on the users and > user_groups tables: > > CREATE TABLE users ( > id serial, > tenant_id int REFERENCES tenants(id), > PRIMARY KEY (tenant_id, id) > );
That is not a proper solution, because it does not guarantee uniqueness of the "id" column, which is typically what you want. So I think Jack's example illustrates the benefit of this proposal well. On the other hand, the SQL standard requires that a foreign key references a unique constraint, see chapter 11.8 <referential constraint definition>, Syntax Rules 3) a): "If the <referenced table and columns> specifies a <reference column list>, then there shall be a one-to-one correspondence between the set of <column name>s contained in that <reference column list> and the set of <column name>s contained in the <unique column list> of a unique constraint of the referenced table such that corresponding <column name>s are equivalent." So while I personally agree that the proposed feature is useful, I am not sure if it is useful enough to break the standard in a way that may be incompatible with future extensions of the standard. Yours, Laurenz Albe
