Take the 2-minute tour ×

I have a table:

table votes (
    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.
share|improve this question
you could modify your question to How do I specify a composite unique constraint in MySQL? –  systemovich Oct 6 '10 at 17:17
@Geoffrey Van Wyk then the people that don't know SQL terminology will never find this question or it's answer. –  Timo Huovinen Feb 1 '12 at 13:43
I just found this question by searching on "mysql unique multiple columns." so yes, the scenario @YuriKolovsky mentioned is possible. Thanks, OP! This is a good starting point, and without this question I wouldn't have know what part of TFM I need to read. –  octern Mar 12 '12 at 21:00

9 Answers 9

up vote 542 down vote accepted
ALTER TABLE `votes` ADD UNIQUE `unique_index`(`user`, `email`, `address`);
share|improve this answer
+1 Perfect, simple answer! –  Chuck Feb 26 '11 at 19:31
Would this work properly with the ON DUPLICATE KEY clause of INSERT statements? That is, if I were trying to insert a row that conflicted with another row's user/email/address values, would the INSERT do the actions specified by the ON DUPLICATE KEY clause instead? –  clizzin May 26 '11 at 2:29
Gary, the combination of three would be unique. –  Russ Oct 25 '11 at 12:26
beautiful answer, made me cry –  Rimbuaj Dec 20 '12 at 10:07
For 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" –  Pakman Jun 3 '13 at 20:42

I have a MySQL table:

CREATE TABLE `content_html` (
  `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 `id_box_elements` (`id_box_elements`,`id_router`)

and the UNIQUE KEY works just as accepted, 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.

share|improve this answer
I 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. :) –  GazB Jan 27 '12 at 11:46
what's with all the backquotes, do they serve some purpose? –  puk May 16 '13 at 6:48
It's output out of Adminer (www.adminer.org) which inserts these backquotes automatically, so there is no problem with colliding mysql keywords used as column names. –  Frodik May 16 '13 at 13:40

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...

share|improve this answer
Two points of clarification: 1) this behavior does not hold for ENGINE BDB, and, 2) this is documented behavior, although, IMHO, not documented overtly enough given how surprising/unpleasant it can be. See MySQL bug 25544 bugs.mysql.com/bug.php?id=25544 for a discussion. –  pilcrow May 7 '10 at 21:41
Thanks for the NULL heads up, addressed the issue i was having... I think I'll go with a hash checksum –  Daniel Doezema Dec 18 '11 at 21:24

Have you tried this ?

UNIQUE KEY `thekey` (`user`,`email`,`address`)
share|improve this answer
I tried this, but it doesn't seem to work. I will try again. –  Niyaz Mar 11 '09 at 19:17
What 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. –  WQC Dec 11 '12 at 15:30
Tried it with MySql 5.6. Works from the first try. Thanks Erick! –  Lawrence Jul 4 '13 at 19:48

This works for mysql version 5.5.32

ALTER TABLE  `tablename` ADD UNIQUE (`column1` ,`column2`);
share|improve this answer

You can add multiple-column unique indexes via phpMyAdmin. (I tested in version 4.0.4)

Navigate to the structure page for your target table. Add a unique index to one of the columns. Expand the Indexes list on the bottom of the structure page to see the unique index you just added. Click the edit icon, and in the following dialog you can add additional columns to that unique index.

share|improve this answer

If you want to avoid duplicates in future. Create another column say id2.

UPDATE tablename SET id2 = id;

Now add the unique on two columns:

alter table tablename add unique index(columnname, id2);
share|improve this answer

For adding unique index following are required:

1) table_name 2) index_name 3) columns on which you want to add index

ALTER TABLE tablename ADD UNIQUE index-name(column1 ,column2,'column3'-----------------'columnN');

In your case we can create unique index as follows:

ALTER TABLE votesADD UNIQUE (user ,email,'address');

share|improve this answer

MySql 5 or higher behaves like this (I've just tested):

  • you can define unique constraints involving nullable columns. Say you define a constraint unique (A, B) where A is not nullable but B is
  • when evaluating such a constraint you can have (A, null) as many times you want (same A value!)
  • you can only have one (A, not null B) pair

Example: PRODUCT_NAME, PRODUCT_VERSION 'glass', null 'glass', null 'wine', 1

Now if you try to insert ('wine' 1) again it will report a constraint violation Hope this helps

share|improve this answer

protected by Second Rikudo Jun 3 '13 at 20:51


Would you like to answer one of these unanswered questions instead?
