CODEWITHSUNDEEP
sqldatabasepostgresqlmany-to-many
Henry
Henry

Reputation: 835

Many to many relation without association table

From my knowledge, if you make many to many relations I always used to use association table. Nevertheless, now I have requirement from business (not sure if they have db knowledge) to create relationships between two tables directly. To me that's not possible. They saying that I have to do as follows, so for example what they saying is:

TableA
ID (PK)
refKey

TableB
ID (PK)
refKey

Make many to many between TableA.refKey and TableB.refKey. Is something like that possible?? I've tried but postgres yield to create unique. And.. if I create unique for any of refKey from my knowledge it will be 1:M or M:1. Am I missing something? Nevertheless if you say that's possible please of sample code to initiate such connection based on that particular example.

Upvotes: 0

Views: 708

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270443

You can create a many-to-many relationship with just two tables. This is not a best practice, but it might be practical in some circumstances.

In Postgres, the simplest way is to have an array of ids that references the second table:

create table a (
   a_id int generated always as identity,
   b_ids[] int
);

Why is this generally a bad idea?

  • You cannot validate the foreign key constraints easily. (Well, you can using a trigger or UDF.)
  • You cannot easily guarantee uniqueness. (Well, you can using a fancy check constraint.)
  • You cannot easily see the "inverse" relationship. (Well, you can using unnest().)
  • You cannot easily index the array elements in a.

In other words, lots of the "built-in" functionality is missing when you do this.

I should note that you can also do this with JSON as well as arrays.

Upvotes: 1

Andronicus
Andronicus

Reputation: 26056

You're right there will only be many-to-one or one-to-many relationships. What you need is a joining table, for example:

create table A_B (
    A_id bigint references A,
    B_id bigint references B
)

Then you can have as many rows with the same A_id or B_id as you want. That results in a many-to-many relationship.

Upvotes: 1

Related Questions