SQL Composite Key

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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);