Search code examples
sqldatabasepostgresqlmany-to-many

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.

Solution

  • 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.