Alexander Bird Alexander Bird - 8 months ago 35
MySQL Question

Alter a live table to make a key non-unique

I saw some other questions related to this, but they were not MySQL.

The database is a live database, so I don't want to delete and recreate the table. I simply want to make a column no longer unique, which is less permissive in nature so it shouldn't cause any problems.


If your column was defined unique using UNIQUE clause, then use:

ALTER TABLE mytable DROP INDEX constraint_name

, or, if your constraint was implicitly named,

ALTER TABLE mytable DROP INDEX column_name

If it was defined unique using PRIMARY KEY clause, use:


Note, however, that if your table is InnoDB, dropping PRIMARY KEY will result in implicit recreation of your table and rebuilding all indexes, which will lock the table and may make it inaccessible for quite a long time.