Drizzle Team•15mo ago

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)
);

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;

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?

11 Replies

francisOP•15mo ago

I can't do the "obvious" solution here and just have c point directly back to a for the one-to-many, since I need to know where each c sits in the ordering of associated b entities; and I need that ordering to be unique, so I need the unique constraint on (a_id, index) across the index values for both B and C.

Angelelz•15mo ago

So the ordering for which you created the index is for both B and C? Your are currently using a_joins table as your intermediary m-2-m table between a and b and to keep track of the index?

francisOP•15mo ago

correct because I need A1 to have B1, B2, B3... and A2 to have B3, B2 B1.... for example the ordering applies to the A-B relationship and the A-C relationship each unique A has a different ordering of B values (which are many-to-many to A) and C values (which are many-to-one to A) which led me to think I need a single table with a unique ordering per A that points to both B and C, with a check constraint ensuring only one is pointed to at a time this all works, actually, the problem I have is defining a relationship such that a C is deleted when the associated A is deleted (since C is many-to-one, not many-to-many, I don't want dangling Cs left around) OH WAIT @angelelz I AM BEING SO DUMB aaaah thank you for rubber ducking I can just have c (id) be a foreign key to a_join (c_id) as well as the primary key there's no need for a separate c (a_join_id) or to try to keep those in sync with the target a_join row @angelelz to be clear I still welcome feedback on if there is a better way to accomplish the goal! - A to B is a many-to-many - A to C is a many-to-one - for any given A, we should have an ordering for Bs and Cs, such that they can be interleaved, e.g. B1, B2, C1, B3, .... I'm pretty sure there's no way to accomplish this without a join table joining each A to either a B or a C, with an ordering on the relationship, no?

Angelelz•15mo ago

My next question was going to be: is the ordering from a to b and c across both?

francisOP•15mo ago

what do you mean by "across both"? if you mean that having e.g. B with order 2 means we cannot have C with order 2, that is correct

Angelelz•15mo ago

Meaning, is it A -> (B1, B2, C1, B3, C3) or A -> (B1, B2, B3) and (C1, C2)

francisOP•15mo ago

correct, it is the former the position of the C in relation to the items of B is important

Angelelz•15mo ago

I think that you have a good solution. I was going to propose to model the relation from a to b as a regular m-2-m with an extra index column, and model from a to c with an extra index column on table c. BUT, you couldn't warranty correct index (ordering) at BD level

francisOP•15mo ago

exactly like, I could have many to many from a to b, and c with a foreign key to A and an index field, but that doesn't prevent index collisions but yeah, I can't believe I didn't realize I could just use c (id primary key and foreign key to a_join) to solve my cascade delete problem .... it does solve my cascade delete problem, right? the goal here is when an a is deleted, all a_join entries are deleted, b entries are _not_ deleted, but c entries are

Angelelz•15mo ago

I gotta think about this. Because for a one to many (a -> c), you put a a_id on c But in this case, you're putting a -> a_joins(a_id) -> a_joins(c_id) -> c I'll think about this

francisOP•15mo ago

also, is there an accepted terminology for what I'm doing here? using a join table for a one-to-many really we can simplify the problem statement, remove B entirely, and just say: I have a join table a_joins which joins a_id and unique c_id, and I want the C to be deleted when the corresponding A is deleted. I am accomplishing this with a_id references a (id) on delete cascade, c_id references c (id) on delete restrict, then a constraint on c: (id) references a_joins (c_id) on delete cascade This seems like an elegant way to accomplish "A links to C, C can't be deleted if a link exists, but when the link is deleted, the target C is also deleted" when I cannot use a foreign key from C direct to A.

Gaming

Programming

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