Is DROP/ADD UNIQUE constraint in ALTER TABLE atomic?

I have a UNIQUE constraint of a number of fields on a table. Image that I want to change the fields in this constraint (remove one field). Normally I would do something like:

DROP INDEX unique_name ON table_name;
CREATE UNIQUE INDEX unique_name ON table_name (field1, field2, ...);

But these are two statements, and after executing the first one there will be no UNIQUE constraint anymore, so any INSERT may potentially break the constraint, until new UNIQUE is created.

There is another way to do that:

ALTER TABLE table_name
DROP INDEX unique_name, 
ADD CONSTRAINT unique_name UNIQUE (field1, field2, ...);

This is one statement. The questions is: will this statement perform an atomic operation? By atomic I mean that there is no time window when there is no UNIQUE constraint at all.

According to the official ALTER TABLE documentation, there should be no issue with such an operation and there is no window when there is no UNIQUE constraint at all. See (emphasis mine):


Storage, Performance, and Concurrency Considerations


In most cases, ALTER TABLE makes a temporary copy of the original table. MySQL waits for other operations that are modifying the table, then proceeds. It incorporates the alteration into the copy, deletes the original table, and renames the new one. While ALTER TABLE is executing, the original table is readable by other sessions (with the exception noted shortly). Updates and writes to the table that begin after the ALTER TABLE operation begins are stalled until the new table is ready, then are automatically redirected to the new table without any failed updates. The temporary copy of the original table is created ...

Of course nothing forbids you from doing the operation in two steps (but this will probably be less efficient).

-
first add the new UNIQUE constraint (with a different name):

ALTER TABLE table_name
    ADD CONSTRAINT unique_name_2 
    UNIQUE (field1, field2, ...) ;

-
the delete the old one:

ALTER TABLE table_name
    DROP INDEX unique_name ;

This allows a small window where you'll have both constraints but again no time where none of them is there.

StackExchange Database Administrators Q#142200, answer score: 6