How do I specify unique constraint for multiple columns in MySQL?

Question

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.

score 1564 · Accepted Answer · edited Mar 18 '21 at 15:12

1564

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`);

edited Mar 18 '21 at 15:12

Persistence

2,502
3
22
58

answered Mar 11 '09 at 19:14

jonstjohn

56,752
8
41
55

35

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 02:29
10

That way combination of three would be unique? Or all individual three columns would be unique? – Gary Lindahl Sep 15 '11 at 00:03
111

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 03 '13 at 20:42
Great! Now, how can I drop it if I don't know the name? – Highly Irregular Oct 07 '13 at 00:31
Highly irregular - 'show index from vote' will give you the key name, or you can name it when you create it. – jonstjohn Oct 09 '13 at 02:50
12

Like 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 http://stackoverflow.com/questions/9537710/is-there-a-way-to-use-on-duplicate-key-to-update-all-that-i-wanted-to-insert – Joe Dec 25 '13 at 23:06
is there any query to get all the column names which forms unique constraint? for above query I wan to know that in votes table user,email,address forms a unique constraint together. – Sanjay Jain May 14 '14 at 08:07
one thing to be noticed is that if any unique index have defined on `user` or `email` or `address` before this, they must be removed to allow this rule works well for cases such as `ON DUPLICATE KEY UPDATE` – VSB Sep 06 '14 at 12:45
3

how to make it work even with NULL value. If we insert Two same rows with NULL value, this is not working... – Wasim A. Jan 19 '15 at 18:50
2

@wasim null is considered to be "a missing unknown value" which means that MySQL does not consider two nulls to be the same. There is no way to enforce this with a unique index. You might be able to accomplish it with a BEFORE INSERT / BEFORE UPDATE trigger but not with a unique index. – jonstjohn Jan 20 '15 at 19:34
Just love this example. I keep coming back to this question time and time again, somehow I just cannot recall the exact syntax, this helps a lot! – Emil Borconi Sep 28 '16 at 10:11
was about to upvote the answer... but there are exactly 1024 votes... >_ – Abraham Murciano Benzadon Jun 21 '17 at 16:46
For those who use phpMyAdmin, go to 'Structure'-->'+ Indexes' below the table and it can be changed from there too. – DimP May 16 '18 at 18:24

score 260 · Answer 2 · edited Apr 24 '17 at 18:44

260

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 `id_box_elements` (`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.

edited Apr 24 '17 at 18:44

jmc

762
9
18

answered Aug 09 '10 at 14:56

Frodik

13,164
22
83
132

39

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
3

what's with all the backquotes, do they serve some purpose? – puk May 16 '13 at 06:48
8

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

score 56 · Answer 3 · answered May 07 '10 at 21:21

56

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

answered May 07 '10 at 21:21

niksoft

585
4
2

11

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 http://bugs.mysql.com/bug.php?id=25544 for a discussion. – pilcrow May 07 '10 at 21:41
3

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
Official docs say: Note that, as of MySQL 5.1, BDB isn't supported any longer. – George D. Jul 19 '18 at 09:59
1

Why is this insane behavior? – cubesnyc Jul 29 '20 at 15:28

score 23 · Answer 4 · answered Mar 11 '09 at 19:14

23

Have you tried this ?

UNIQUE KEY `thekey` (`user`,`email`,`address`)

answered Mar 11 '09 at 19:14

Erick

5,641
10
39
59

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 04 '13 at 19:48

score 13 · Answer 5 · edited Feb 05 '14 at 08:55

13

This works for mysql version 5.5.32

ALTER TABLE  `tablename` ADD UNIQUE (`column1` ,`column2`);

edited Feb 05 '14 at 08:55

Code Lღver

15,171
16
51
71

answered Jan 02 '14 at 19:00

rizon

7,731
1
24
16

1

It seems to work without the single quotes on the column name. – Pratik Singhal Nov 24 '16 at 06:40

score 7 · Answer 6 · answered Aug 03 '14 at 12:56

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

thanks for explaining. I have unique(A,B,C) and I didn't know why it allowed combinations of null with same non-null options — Alexandru Topală, Oct 18 '19 at 07:15

score 6 · Answer 7 · answered Mar 20 '14 at 00:40

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.

score 5 · Answer 8 · answered Jun 27 '18 at 21:20

5

I do it like this:

CREATE UNIQUE INDEX index_name ON TableName (Column1, Column2, Column3);

My convention for a unique index_name is TableName_Column1_Column2_Column3_uindex.

answered Jun 27 '18 at 21:20

Sam Malayek

2,963
2
24
40

score 3 · Answer 9 · edited Nov 22 '16 at 14:07

3

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 `votes`ADD 
UNIQUE <votesuniqueindex>;(`user` ,`email`,`address`);

edited Nov 22 '16 at 14:07

TheGameiswar

25,396
5
48
82

answered Jul 10 '14 at 08:22

sandeep vanama

609
8
8

score 3 · Answer 10 · answered Oct 09 '18 at 05:50

If You are creating table in mysql then use following :

create table package_template_mapping (
mapping_id  int(10) not null auto_increment  ,
template_id int(10) NOT NULL ,
package_id  int(10) NOT NULL ,
remark      varchar(100),
primary key (mapping_id) ,
UNIQUE KEY template_fun_id (template_id , package_id)
);

score 2 · Answer 11 · edited Mar 09 '13 at 23:01

2

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);

edited Mar 09 '13 at 23:01

Taryn

224,125
52
341
389

answered Mar 09 '13 at 13:04

kumar

21
1

gandalivs · Answer 12 · 2020-08-31T06:50:17.900

2

this tutorial works for me

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n);

https://www.mysqltutorial.org/mysql-unique-constraint/

edited Aug 31 '20 at 06:50

answered Aug 31 '20 at 02:57

gandalivs

101
1
3

score 0 · Answer 13 · answered Jan 13 '20 at 18:32

First get rid of existing duplicates

delete a from votes as a, votes as b where a.id < b.id 
and a.user <=> b.user and a.email <=> b.email 
and a.address <=> b.address;

Then add the unique constraint

ALTER TABLE votes ADD UNIQUE unique_index(user, email, address);

Verify the constraint with

SHOW CREATE TABLE votes;

Note that user, email, address will be considered unique if any of them has null value in it.

score 0 · Answer 14 · edited May 06 '20 at 11:39

0

For PostgreSQL... It didn't work for me with index; it gave me an error, so I did this:

alter table table_name
add unique(column_name_1,column_name_2);

PostgreSQL gave unique index its own name. I guess you can change the name of index in the options for the table, if it is needed to be changed...

edited May 06 '20 at 11:39

David Buck

3,439
29
24
31

answered May 06 '20 at 11:01

bugthefifth

91
9

Question is not for `PostgreSQL` database, mentioned tag is `Mysql`. – dipenparmar12 Dec 09 '20 at 09:02
As I remember, I just did this answer, because on my specific search for PostgreSQL, this answer was at the top. So I mentioned the solution for PostgreSQL just in case somebody like me ends up here, tries the solution for MySQL (at the end of the day it's all SQL) and the original solution doesn't work for them, because PostgreSQL nuances. – bugthefifth Dec 09 '20 at 12:55

How do I specify unique constraint for multiple columns in MySQL?

14 Answers14

Linked

Related