How do I specify unique constraint for multiple columns in MySQL?
Solution 1
To add a unique constraint, you need to use two components:
ALTER TABLE
- to change the table schema and,
ADD UNIQUE
- to add the unique constraint.
You then can define your new unique key with the format 'name'('column1', 'column2'...)
So for your particular issue, you could use this command:
ALTER TABLE `votes` ADD UNIQUE `unique_index`(`user`, `email`, `address`);
Solution 2
I have a MySQL table:
CREATE TABLE `content_html` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`id_box_elements` int(11) DEFAULT NULL,
`id_router` int(11) DEFAULT NULL,
`content` mediumtext COLLATE utf8_czech_ci NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `my_uniq_id` (`id_box_elements`,`id_router`)
);
and the UNIQUE KEY works just as expected, it allows multiple NULL rows of id_box_elements and id_router.
I am running MySQL 5.1.42, so probably there was some update on the issue discussed above. Fortunately it works and hopefully it will stay that way.
Solution 3
Multi column unique indexes do not work in MySQL if you have a NULL value in row as MySQL treats NULL as a unique value and at least currently has no logic to work around it in multi-column indexes. Yes the behavior is insane, because it limits a lot of legitimate applications of multi-column indexes, but it is what it is... As of yet, it is a bug that has been stamped with "will not fix" on the MySQL bug-track...
Solution 4
Have you tried this ?
UNIQUE KEY `thekey` (`user`,`email`,`address`)
Solution 5
This works for mysql version 5.5.32
ALTER TABLE `tablename` ADD UNIQUE (`column1` ,`column2`);
Comments
-
Niyaz almost 2 years
I have a table:
table votes ( id, user, email, address, primary key(id), );
Now I want to make the columns user, email, address unique (together).
How do I do this in MySql?
Of course the example is just... an example. So please don't worry about the semantics.
-
GazB over 12 yearsI wanted to mark this answer up as it does show you how to create a new table with a unique index where as jonstjohn's answer above tells you how to update an existing table. They do the same thing though just depends on if your creating a new table or updating an existing one. :)
-
WQC over 11 yearsWhat Erick said works just fine for me:
UNIQUE KEY
thekey` (user
,email
,address
)`. I'm using MYSQL version 5.5.24 You can set this even in PHPMYADMIN if you are using it. I'm using the 3.5.1 version of PMA. -
Pakman almost 11 yearsFor those people using MySQL workbench: go to the Indexes tab and select UNIQUE as your type. Give your index a name and check the appropriate columns under the section "Index Columns"
-
Lawrence almost 11 yearsTried it with MySql 5.6. Works from the first try. Thanks Erick!
-
Joe over 10 yearsLike me, if anyone else found this from search engines... and to answer Clizzin's question about if ON DUPLICATE KEY will work with a composite key - it absolutely will, however it will require some minor adjustments to the syntax. see stackoverflow.com/questions/9537710/…
-
Adam F over 2 yearsVery interesting, I can confirm that I can have multiple null values in unique columns in MySQL 5.7.37