Drizzle TeamDT
Drizzle Team•3y ago
francis

Creating join table that is simultaneously many-to-many and one-to-many

got a data modeling / sql question for you all:
I have an entity A with either a many-to-many relationship to B, or a one-to-many relationship to C. There must be a unique ordering of all relationships from A to B or C, such that we can define an ordering such as B1, B2, C1, B3, C2... etc

B and C are different entities, with wildly different table structures, and therefore cannot be combined into a single table.

I am solving this with:
A, B, and C all have 
id
 primary key fields.
I create a table a_joins as:
create table a_joins (
    id uuid primary key default uuid_generate_v4(),
    a_id uuid not null references a (id) on delete cascade,
    b_id uuid references b (id) on delete restrict,
    c_id uuid unique references c (id) on delete restrict,
    index integer not null,
    check ((b_id is not null) != (c_id is not null)),
    unique (a_id, b_id),
    unique (a_id, index)
);


This allows me to ensure an ordering for all a_id entries, such that each entry in the ordering points to either a b (shared by many a) or a c (for this a only).

The problem I have is that I want cascade delete behavior, such that on deletion of a, the a_joins entries are deleted (which works due to cascade), and any c associated with a is deleted as well.

How would you recommend I accomplish this? I attempted to add a foreign key relationship to c to perform the cascade:

alter table c add constraint c_a_fkey
foreign key (id, a_id)
references a_joins (c_id, id) on delete cascade;


This should ensure both that a c entry only ever points to the single unique a_joins entry that references it, and that the c entry is deleted when the corresponding a_joins entry is removed. However, this fails due to the lack of a unique index on a_joins (c_id, id), even though both of those columns are individually marked as either unique or primary key. How is this possible?
Was this page helpful?