PostgreSQL – Single Foreign Key Referencing Multiple Tables

many-to-manypostgresql

Currently I have a schema that looks like this:

create table person(
    id uuid primary key default gen_random_uuid() not null,
);

create table car(
    id uuid primary key default gen_random_uuid() not null,
);

create table extra(
    id uuid primary key default gen_random_uuid() not null,
);

create table car_extra_m2m(
    car_id uuid not null references car(id) on delete cascade,
    extra_id uuid not null references extra(id) on delete cascade,
    primary key (car_id, extra_id)
);

create table person_extra_m2m(
    person_id uuid not null references person(id) on delete cascade,
    extra_id uuid not null references extra(id) on delete cascade,
    primary key (person_id, extra_id)
);

Is it possible to express the two many to many tables in a single many to many table? Since we're using uuid the ids should never collide, so it might be possible to know the type just from the uuid?

Like this pseudocode: (Edit: not valid syntax in postgresql, does there exist a valid syntax for this?)

create table extra_m2m(
    person_or_car_id uuid not null references (person(id) or car(id)) on delete cascade,
    extra_id uuid not null references extra(id) on delete cascade,
    primary key (person_or_car_id, extra_id)
);

Best Answer

Yea there's no reason not to if it fits your use case better, there's nothing wrong with that design. My only suggestion would to add a extra_type field to explicitly identify whether the record is of type person or car. You'll likely find a field like that will be helpful later on. But yes that's a valid schema design.

After further clarification in the comments, you'd have to make a couple changes to natively accomplish this to still be able to support foreign key constraints in the database (otherwise what you'd be looking for is a polymorphic foreign key which isn't natively supported).

The first change is you'd have to merge your person and car table into a single table, e.g. person_or_car with the person_or_car_id primary key field. The second change is you'd need to add an extra_type_id field to the person_or_car table to distinguish between the two. And finally you'll need to add the extra_type_id field to the extra_m2m table, and create the foreign key constraint on it.

Example query with the extra_type_id field, as discussed in the comments, for step 3:

create table extra_m2m
(
    person_or_car_id uuid not null,
    extra_type_id int,
    extra_id uuid not null references extra(id) on delete cascade,
    primary key (person_or_car_id, extra_id),
    foreign key (person_or_car_id, extra_type_id) references person_or_car (person_or_car_id, extra_type_id) on delete cascade
);
Related Question