CODEWITHSUNDEEP
mysqldatabase-designprimary-key
ForeverNoobie
ForeverNoobie

Reputation: 531

Mysql composite primary key

I want to have a lookup table that links two of the same things to eachother. Say I have a 'Person' table and I want to lookup the relationship between two people. I'll have column one of the lookup be 'PersonId1' and column two be 'PersonId2' and the third column be 'Relationship'. Since the relationship goes both ways I don't need to have duplicate records with the PlayerId's switched. Is there any way to make mysql enforce uniqueness on PlayerId1 and PlayerId2 combinations regardless of which order they're in?

Does that make sense?

Upvotes: 1

Views: 969

Answers (3)

user2072139
user2072139

Reputation: 61

+1 for composite pk. To prevent duplicate combinations, an extra varchar column with for example personid1+personid2 with a unique constraint on it may be a solution...

See also: person data model example

Upvotes: 0

O. Jones
O. Jones

Reputation: 108676

Short answer: No.

Longer answer: You could set up a trigger to swap the order of the two person ids if the second were smaller than the first, then write them, and use a composite key.

Even longer answer: Not all interpersonal relationships are commutative (not all relationships go both ways). What about the "Employee" or "Mother" relationships? Even the "Friend" relationship, which is presumably peer-to-peer, might be better represented if you had separate rows saying A is B's Friend and B is A's Friend. So maybe you want a three-field composite key on this table.

Upvotes: 2

Freddie Fabregas
Freddie Fabregas

Reputation: 1203

You mean you want to have a unique row record from PersonID1 and PersonID2 Column (regardless of the Relationship column)? If that so, you may use the Composite key (Multi column key).

Here's an example:

CREATE TABLE Person (
    PersonId1 INT,
    PersonId2 INT,
    PRIMARY KEY (PersonId1, PersonId2)
)

Upvotes: 0

Related Questions