Alexander Bird Alexander Bird - 1 year ago 74
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.

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download