Enforce subset relationship in postgres - Postgresql

Arnoux
December 10, 2022
255 views
1 vote
2 Answers

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.

Answers

Chosen as BEST ANSWER

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

(Edit)

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.

Please signup or login to give your own answer.

Click here to cancel reply.

Enforce subset relationship in postgres – Postgresql

Answers