CODEWITHSUNDEEP
mysqlsqlrelational-database
Eugene
Eugene

Reputation: 1427

MySQL Dual Column Primary Key Dilemma

Here's the situation -> I'm working with two columns of data (A and B - both ints) in a MySQL environment. A and B are non-unique attributes, but each row/slice of A & B has to be unique.

For example these are valid entries:

A      B
---    ---
1      1
1      2
1      3

In this case below the third row in invalid cause it breaks the rule that each combo of A and B has to be unique:

A      B
---    ---
1      2
1      3
1      2

What's the best way for me to model this type of relationship in a MySQL setting where the combined effect of two columns creates a primary key? Can I reduce this logic down to one primary key? In that case, how do I make sure that each new row doesn't violate the rule?

Upvotes: 0

Views: 130

Answers (2)

mainstreetmark
mainstreetmark

Reputation: 703

Try a unique index:

 ALTER TABLE `table` ADD UNIQUE INDEX (`A`, `B`);

You will have to de-dup the table before this query will work, but once it does, then you are guaranteed to have unique A+B rows in the table. Queries can use both, but do not have to:

 SELECT * from `table` WHERE `A`=1 and `B`=2;

Upvotes: 1

STLDev
STLDev

Reputation: 6164

You can either create a new, arbitrary key value (typically a sequential integer), or you can create a composite key from the two columns.

Upvotes: 1

Related Questions