CODEWITHSUNDEEP
sqldatabase-designconstraintsprimary-key
user2812201
user2812201

Reputation: 437

SQL Composite Key

I am a SQL newbie and here is a question that I cannot find a solution yet.

Say I have tables User and Partner. I want to have a composite key for Partner, which is a pair of User's. So I can have keys (A,B) and (B,A) where A and B are both users. I want to have that (A,B) and (B,A) refer to the same Partner entity. This seems to be a common problem but I could not find an answer.

Ok, more concretely,

CREATE TABLE User
(
    ID int,
    ....
)
PRIMARY KEY (ID);

CREATE TABLE Partner
(
    User1 UserID,
    User2 UserID
    ...
)
PRIMARY KEY (User1, User2)

What I want is that (User1, User2) and (User2, User1) represent the same partnership.

Upvotes: 1

Views: 177

Answers (3)

nvogel
nvogel

Reputation: 25526

If you want to ensure that both pairs must be included in the table then just add another constraint:

ALTER TABLE Partner ADD FOREIGN KEY (User2,User1) REFERENCES Partner (User1,User2);

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269603

You have two options. One is to ensure that both (a, b) and (b, a) are in the table. You can do this using a trigger when you insert a row.

The second is to include only one of the pair -- normally as an ordered pair.

Basically, it depends on how you are going to use the table. The first method requires twice as much storage, but it is simpler to get all the partners of a given user.

Upvotes: 1

Mureinik
Mureinik

Reputation: 311163

Instead of trying to to make (A,B) and (B,A) considered as the same, you can take an arbitrary decision to always have A have the lower ID (*), and back it up with constraints;

CREATE TABLE partner (
    a INT NOT NULL,
    b INT NOT NULL,
    PRIMARY KEY (a, b),
    CHECK (a < b),
    FOREIGN KEY (a) REFERENCES user(id),
    FOREIGN KEY (b) REFERENCES user(id)
)

(*) Or the larger one - it doesn't really matter, as long as you're consistent.

Upvotes: 2

Related Questions