Is it possible to alter a MySQL unique index to non-unique without recreating it

Viewed 635

Is it possible to alter a MySQL unique index to non-unique without recreating it (DROP and CREATE)?

I'm using MySQL version 8.0.19.

2 Answers

No, in MySQL it is not possible to change the definition of an index without dropping it and recreating it. There is no ALTER INDEX statement.

Correction: You can rename an index without rebuilding it. You can make an index visible/invisible without rebuilding it.

If you've meant that you want to avoid dropping the unique index, then please read Bill Karwin's answer, which properly describes that it's not possible.

However, if you've meant that you want to recreate an index without dropping and recreating the table, then it's certainly possible, like:

DROP INDEX indexname on tablename;

and then

CREATE UNIQUE indexname ON tablename(columnname);
Related