CODEWITHSUNDEEP
sqldatabasepostgresqldatabase-design
Aaron
Aaron

Reputation: 57

Enforcing unique rows in a many-to-many junction table

I have a junction table for a many-to-many relationship that just links two foreign keys together. However I've found that this will not prevent duplicate identical row entries. What's the proper way of handling that? I thought adding PRIMARY KEY to the two foreign keys would do it, but it seems like I'm not understanding that correctly.

CREATE TABLE ab_link (
    a_id     bigint REFERENCES a(a_id) PRIMARY KEY,
    b_id     bigint REFERENCES b(b_id) PRIMARY KEY
);

I found on another question this example:

CREATE TABLE bill_product (
  bill_id    int REFERENCES bill (bill_id) ON UPDATE CASCADE ON DELETE CASCADE
, product_id int REFERENCES product (product_id) ON UPDATE CASCADE
, amount     numeric NOT NULL DEFAULT 1
, CONSTRAINT bill_product_pkey PRIMARY KEY (bill_id, product_id)  -- explicit pk
);

Is that constraint the best way of enforcing uniqueness? I would think there would be some way of doing it without having a third row.

Upvotes: 0

Views: 397

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

For a compound primary key, you need a separate declaration:

CREATE TABLE ab_link (
    a_id     bigint REFERENCES a(a_id),
    b_id     bigint REFERENCES b(b_id),
    PRIMARY KEY (a_id, b_id)
);

Upvotes: 4

Related Questions