This is one of those things that is easy to do, but I frequently forget because of how little I deal with SQL syntax. Essentially, there are times when you want two fields to be unique together. For example, you might have a table with a city field and a state field. It's okay to have an entry for Portland, Oregon and an entry for Portland, Maine, but it is not okay to have Portland, Oregon more than once in the table. This is when you need a unique key pair (or it could be any number of keys that are unique together).
First, we create an overly simple example table:
-
CREATE TABLE `test`.`exampletable` (
-
`value1` INT UNSIGNED NOT NULL ,
-
`value2` INT UNSIGNED NOT NULL ,
-
UNIQUE KEY `value1` (`value1`, `value2`)
-
) ENGINE = MYISAM ;
Then we plug in some basic values:
-
INSERT INTO `test`.`exampletable` (
-
`value1` ,
-
`value2`
-
)
-
VALUES (
-
'1', '1'
-
), (
-
'1', '2'
-
), (
-
'2', '1'
-
), (
-
'2', '2'
-
);
That should show you something like 4 row(s) inserted. ( Query took 0.0005 sec ). Now you have 1,1; 1,2; 2,1; and 2,2 stored in the table. What happens when you insert 1,1 again?
-
INSERT INTO `test`.`exampletable` (
-
`value1` ,
-
`value2`
-
)
-
VALUES (
-
'1', '1'
-
);
You should see this error message: Duplicate entry '1-1' for key 1. Yup, that's all there is to creating unique key pairs in MySQL.
Thanks, really helped me
was looking for this FOREVER!
i wonder why this was so difficult to find.
needed 2 columns UNIQUE together and got EXACTLY WHAT I WANT. works like a charm.
thanks