Is it possible to create a unique index in MySQL if the table repeats the values?

Question

For an existing table, is it permissible to create a unique column that can have duplicate values?

+5

Leo romanovsky Apr 24 '13 at 1:38

3 answers

This is actually possible, at least in MySQL 5.1+. You can use:

ALTER IGNORE TABLE `table` ADD UNIQUE INDEX (`column`);

If your table is InnoDB, you will need to execute it first:

set session old_alter_table=1

EDIT: MySQL 5.7.4 IGNORE ALTER TABLE , . @EdwardvanKuik

+2

MatthewLee 28 '14 22:57

It's impossible:

Mysql2::Error: Duplicate entry 'foo' for key 'index_clubs_on_url': CREATE UNIQUE INDEX `index_clubs_on_url` ON `clubs` (`url`)

0

Leo romanovsky Apr 24 '13 at 1:45

Danny beckett · Accepted Answer · 2013-04-24T01:45:38+0000

No, this is unacceptable.

Next SQL:

ALTER TABLE `table`
ADD UNIQUE (`column`)

Will generate the following error:

# 1062 - Duplicate the 'data' entry for the key column

You can identify duplicates using:

SELECT * FROM `table`
GROUP BY `column`
HAVING COUNT(`column`) > 1

After deleting all duplicates, you can add a restriction UNIQUE.