Enforce subset relationship in postgres

Question

How can I enforce the following business logic in my database schema, without using application code:

a user must belong to exactly one company
a company can have multiple vehicles
a vehicle can have multiple companies
a user can have multiple vehicles, but these vehicles must be a subset of the vehicles that are linked to the user's company (i.e. a user cannot be linked to a vehicle that it's company does not have access to)

This is what I have so far:

create table companies (
    id serial primary key,
    name varchar not null,
    unique(name)
);

create table vehicles (
    id serial primary key,
    name varchar not null,
    unique(name)
);

create table users (
    id serial primary key,
    company_id integer not null,
    name varchar not null,
    foreign key (company_id) references companies(id)
);

create table vehicle_companies (
    id serial primary key,
    vehicle_id integer not null,
    company_id integer not null,
    foreign key (vehicle_id) references vehicles(id),
    foreign key (company_id) references companies(id)
);

create table user_vehicles (
    user_id integer not null,
    vehicle_company_id integer not null,
    foreign key (user_id) references users(id),
    foreign key (vehicle_company_id) references vehicle_companies(id)
);

How can I ensure that all times the rows in the user_vehicles table only reference vehicle_companies records that have a company_id which is the same as their referenced users table records' company_id?

I am using postgres.

It's possible to enforce all these rules, by expanding the primary key of `users` to include the `company_id`. Then , `user_vehicles` will include `(user_id, company_id)` as an fk to `user`, and at the same time `(id, company_id)` from `vehicle_compaies`; you would notice that those two fks share the `company_id` column. That's how it can be enforced at the db level. — The Impaler, Dec 10 '22 at 21:00
Yes, expanding the primary key satisfies the `company_id` requirement between `user_vehicles` and `vehicle_companies`. However, *a user must belong to exactly one company* is no longer enforced. Which means not only can a user be with multiple companies but `user_vehicles` can reference each of those same companies. — Belayer, Dec 11 '22 at 02:17
@Impaler thank you for your input. Would you perhaps be willing to put together a demo? — Arnoux, Dec 12 '22 at 10:19
@TheImpaler I believe my accepted solution is similar to what you were describing? — Arnoux, Dec 16 '22 at 10:11

score 1 · Answer 1 · answered Dec 11 '22 at 02:28

You are trying enforce referential integrity across independent entities (tables). You cannot do that with simple constraints (perhaps an Exclude Constraint - but I do not think so). To accomplish that you will need a trigger on user_vehicles.

create or replace function validate_user_vehicle_company() 
    returns trigger 
   language plpgsql
as $$
begin 
    if not exists( select null 
                     from users u 
                     join vehicle_companies v 
                       on ( v.company_id = u.company_id) 
                    where u.id  = new.user_id
                      and v.company_id = new.vehicle_company_id 
                      and v.vehicle_id = new.vehicle_company_id     
                 ) 
    then 
       raise exception E'During % on %.%\nUser.company_id does not match vehicle_companies.company_id' ,tg_op,tg_table_schema,tg_table_name; 
    end if;

     return new;
end;
$$;

create or replace trigger user_vehicles_biur
   before insert or update 
   on user_vehicles 
   for each row 
   execute function validate_user_vehicle_company();

I have put together a short demo here, with very simple data values.

thanks for your effort. This gets me most of the way, but unfortunately it is still possible for a `vehicle_companies` record to exist that references a `company_id` that is different to the `company_id` of the `users` record when the `company_id` of the `vehicle_companies` record is updated after the `user_vehicles` table record referencing it is created. I've updated your demo [here](https://dbfiddle.uk/-oqi5jo4) to show what I mean. — Arnoux, Dec 12 '22 at 10:03
@Arnoux Yes, that's the way of doing it. I've seen some solutions using composite primary keys and other ones (like yours) using a separate unique constraint. Both work well. — The Impaler, Dec 18 '22 at 01:02

score 1 · Accepted Answer · answered Dec 16 '22 at 10:07

This schema solved it for me:

create table companies (
    id serial primary key,
    name varchar not null,
    unique(name)
);

create table vehicles (
    id serial primary key,
    name varchar not null,
    unique(name)
);

create table users (
    id serial primary key,
    company_id integer not null,
    name varchar not null,
    unique (id, company_id),
    foreign key (company_id) references companies(id)
);

create table vehicle_companies (
    id serial primary key,
    vehicle_id integer not null,
    company_id integer not null,
    unique (vehicle_id, company_id),
    foreign key (vehicle_id) references vehicles(id),
    foreign key (company_id) references companies(id)
);

create table user_vehicles (
    id serial primary key,
    user_id integer not null,
    vehicle_id integer not null,
    company_id integer not null,
    unique (id),
    unique (vehicle_id, company_id),
    foreign key (user_id, company_id) references users(id, company_id),
    foreign key (vehicle_id, company_id) references vehicle_companies(vehicle_id, company_id)
);

DB fiddle

Enforce subset relationship in postgres

2 Answers2