MySQL - Make an existing Field Unique

mysql unique-constraint
Lothar picture Lothar · Feb 18, 2011 · Viewed 181.9k times · Source

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?

Answer

WuHoUnited picture WuHoUnited · Feb 18, 2011 
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

Related questions

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

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

Read More
MySQL delete multiple rows in one query conditions unique to each row

So I know in MySQL it's possible to insert multiple rows in one query like so: INSERT INTO table (col1,col2) VALUES (1,2),(3,4),(5,6) I would like to delete multiple rows in a similar way. I know it's possible to delete multiple …

Read More
Does MySQL ignore null values on unique constraints?

I have an email column that I want to be unique. But I also want it to accept null values. Can my database have 2 null emails that way?

Read More