31 replies

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

id

primary key fields.
I create a table

a_joins

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

a_id

entries, such that each entry in the ordering points to either a

(shared by many a) or a

(for this a only).

The problem I have is that I want cascade delete behavior, such that on deletion of

, the

a_joins

a_joins

entries are deleted (which works due to cascade), and any

associated with

is deleted as well.

How would you recommend I accomplish this? I attempted to add a foreign key relationship to

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

entry only ever points to the single unique

a_joins

a_joins

entry that references it, and that the

entry is deleted when the corresponding

a_joins

a_joins

entry is removed. However, this fails due to the lack of a unique index on

a_joins (c_id, id)

a_joins (c_id, id)

, even though both of those columns are individually marked as either unique or primary key. How is this possible?

Drizzle Team•3y ago•

31 replies

francis

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

id

id

primary key fields.
I create a table

a_joins

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

a_id

entries, such that each entry in the ordering points to either a

(shared by many a) or a

(for this a only).

The problem I have is that I want cascade delete behavior, such that on deletion of

, the

a_joins

a_joins

entries are deleted (which works due to cascade), and any

associated with

is deleted as well.

How would you recommend I accomplish this? I attempted to add a foreign key relationship to

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

entry only ever points to the single unique

a_joins

a_joins

entry that references it, and that the

entry is deleted when the corresponding

a_joins

a_joins

entry is removed. However, this fails due to the lack of a unique index on

a_joins (c_id, id)

a_joins (c_id, id)

, even though both of those columns are individually marked as either unique or primary key. How is this possible?

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

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

Similar Threads

Similar Threads

Similar Threads