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

15

1048

I have a table:

table votes (
    id,
    user,
    email,
    address,
    primary key(id),
);

How can I make the columns user, email, address unique - i.e., ensure that there isn't any pair of rows that has identical values for all three columns?

Disoperation answered 11/3, 2009 at 19:12 Comment(0)
D
1695

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`);
Doorstop answered 11/3, 2009 at 19:14 Comment(2)
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"Codi
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 #9538210Ceilometer
C
316

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.

Crofton answered 9/8, 2010 at 14:56 Comment(2)
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. :)Flounce
Why UNIQUE KEY my_uniq_id? Just UNIQUE is fine (on current MariaDB, anyway).Inearth
A
64

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

Allineallis answered 7/5, 2010 at 21:21 Comment(2)
Very interesting, I can confirm that I can have multiple null values in unique columns in MySQL 5.7.37Grandpa
This is not a bug you can fix this problem by composite primary keyIncentive
A
27

Have you tried this ?

UNIQUE KEY `thekey` (`user`,`email`,`address`)
Adaurd answered 11/3, 2009 at 19:14 Comment(1)
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.Carpo
S
21

This works for mysql version 5.5.32

ALTER TABLE  `tablename` ADD UNIQUE (`column1` ,`column2`);
Switchblade answered 2/1, 2014 at 19:0 Comment(0)
H
9

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

Heeler answered 3/8, 2014 at 12:56 Comment(0)
B
6

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.

Barnyard answered 20/3, 2014 at 0:40 Comment(0)
S
6

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/

Schinica answered 31/8, 2020 at 2:57 Comment(0)
D
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.

Dog answered 27/6, 2018 at 21:20 Comment(0)
R
4

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)
);
Ramonitaramos answered 9/10, 2018 at 5:50 Comment(0)
S
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`);
Sham answered 10/7, 2014 at 8:22 Comment(0)
U
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);
Unzip answered 9/3, 2013 at 13:4 Comment(0)
D
0

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.

Deroo answered 13/1, 2020 at 18:32 Comment(0)
D
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...

Dulce answered 6/5, 2020 at 11:1 Comment(0)
L
0

In MySQL Workbench:

  1. Make an Index
  2. Select the Columns
  3. From the Type menu choose UNIQUE

MySQL Workbench GUI - Indexes tab

Lazulite answered 6/11, 2023 at 0:2 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.