169

I have an already existing table with a field that should be unique but is not. I only know this because an entry was made into the table that had the same value as another, already existing, entry and this caused problems.

How do I make this field only accept unique values?

Jimbo
  • 24,043
  • 14
  • 77
  • 118
Lothar
  • 3,209
  • 8
  • 37
  • 56

7 Answers7

315
ALTER IGNORE TABLE mytbl ADD UNIQUE (columnName);

For MySQL 5.7.4 or later:

ALTER TABLE mytbl ADD UNIQUE (columnName);

As of MySQL 5.7.4, the IGNORE clause for ALTER TABLE is removed and its use produces an error.

So, make sure to remove duplicate entries first as IGNORE keyword is no longer supported.

Reference

Shaharyar
  • 11,393
  • 3
  • 39
  • 59
WuHoUnited
  • 7,729
  • 3
  • 22
  • 27
  • 63
    +1 `If IGNORE is specified, only the first row is used of rows with duplicates on a unique key, The other conflicting rows are deleted.` [Docs](http://dev.mysql.com/doc/refman/5.1/en/alter-table.html) – The Scrum Meister Feb 18 '11 at 05:47
  • 1
    Is there a way to tell it to delete rows based on a certain criteria? Or would you need to do some preprocessing before the `ALTER` to get this done. – corsiKa Feb 18 '11 at 05:54
  • I don't know if there is a way to choose the one to keep during this query. `UNIQUE` is one of those things that should really have been done during the creation of the database. – WuHoUnited Feb 18 '11 at 06:00
  • I find I cannot up up-vote enough, this answer has saved my skin today. – clockworkgeek Sep 08 '12 at 11:42
  • 7
    `ALTER IGNORE` may still give Duplicate error on MySQL version > 5.5 and on InnoDB for the safety sake of your data. If you are just sure that keeping the first of the duplicated rows is the right thing, try `set session old_alter_table=1`. Don't forget to set it back. http://mysqlolyk.wordpress.com/2012/02/18/alter-ignore-table-add-index-always-give-errors/ – michaelliu May 11 '13 at 06:39
  • 2
    For anybody stumbling upon this using 5.6.7 or greater, IGNORE is no longer supported. [moredocs](http://dev.mysql.com/doc/refman/5.6/en/alter-table.html) I guess the way to do it now is to make sure you don't have duplicates first, no? – William T. Mallard Apr 16 '14 at 20:04
  • 1
    Be sure to make a backup copy of the table before you run this query. It will delete all the rows except the first one if the columnname has same values or is empty. – steady_daddy Aug 25 '16 at 07:25
37

Just write this query in your db phpmyadmin.

ALTER TABLE TableName ADD UNIQUE (FieldName)

Eg: ALTER TABLE user ADD UNIQUE (email)

Pavindu
  • 1,393
  • 5
  • 18
  • 41
Muhammad Rizwan Kaim Khani
  • 1,953
  • 3
  • 25
  • 35
16

If you also want to name the constraint, use this:

ALTER TABLE myTable
  ADD CONSTRAINT constraintName 
    UNIQUE (columnName);
ypercubeᵀᴹ
  • 105,605
  • 14
  • 160
  • 222
7

CREATE UNIQUE INDEX foo ON table_name (field_name)

You have to remove duplicate values on that column before executes that sql. Any existing duplicate value on that column will lead you to mysql error 1062

Adi Prasetyo
  • 874
  • 1
  • 11
  • 35
Zulan
  • 20,904
  • 6
  • 41
  • 90
4

The easiest and fastest way would be with phpmyadmin structure table.

USE PHPMYADMIN ADMIN PANEL!

There it's in Russian language but in English Version should be the same. Just click Unique button. Also from there you can make your columns PRIMARY or DELETE.

Matt
  • 7,915
  • 2
  • 18
  • 33
MKA
  • 59
  • 1
2
ALTER IGNORE TABLE mytbl ADD UNIQUE (columnName);

is the right answer

the insert part 

INSERT IGNORE INTO mytable ....
user3772326
  • 38
  • 3
1

This code is to solve our problem to set unique key for existing table 

alter ignore table ioni_groups add unique (group_name);
KARTHIKEYAN.A
  • 11,752
  • 4
  • 81
  • 93