Search code examples
mysqluniquealter-table

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.

Solution

  • 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:

    ALTER TABLE mytable DROP PRIMARY KEY

    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.