Why does MySQL use a temporary table to delete the primary key?

Question

When using the command:

  ALTER TABLE my_table DROP PRIMARY KEY;

The state (when SHOW PROCESSLIST) is displayed as:

  copy to tmp table

Why would he use the tmp table to “lower” the primary key constraint?

Answer 1

Consider the case of a composite primary key. In this case, the database engine should create a new clustered index from the synthetic key, which will require moving rows around. (Keep in mind that the strings are physically arranged on the disk by the primary key.) Given the rarity of this situation, you really should not handle the special case when your primary key is already an integer.