Generating and Using Sequences - MySQL - page 459

sequence. The following example shows how to renumber the id values in the insect
table using this technique:
mysql> ALTER TABLE insect DROP id;
mysql> ALTER TABLE insect
-> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
-> ADD PRIMARY KEY (id);
The first ALTER TABLE statement gets rid of the id column (and as a result also drops the
PRIMARY KEY , because the column to which it refers is no longer present). The second
statement restores the column to the table and establishes it as the PRIMARY KEY . (The
FIRST keyword places the column first in the table, which is where it was originally.
Normally, ADD puts columns at the end of the table.)
When you add an AUTO_INCREMENT column to a table, MySQL automatically numbers
all the rows consecutively, so the resulting contents of the insect table look like this:
mysql> SELECT * FROM insect ORDER BY id;
+----+-------------------+------------+------------+
| id | name | date | origin |
+----+-------------------+------------+------------+
| 1 | housefly | 2014-09-10 | kitchen |
| 2 | grasshopper | 2014-09-10 | front yard |
| 3 | stink bug | 2014-09-10 | front yard |
| 4 | cabbage butterfly | 2014-09-10 | garden |
| 5 | ant | 2014-09-10 | back yard |
| 6 | cricket | 2014-09-11 | basement |
| 7 | moth | 2014-09-14 | windowsill |
+----+-------------------+------------+------------+
One problem with resequencing a column using separate ALTER TABLE statements is that
the table is without that column for the interval between the two operations. This might
cause difficulties for other clients that try to access the table during that time. To prevent
this from happening, perform both operations with a single ALTER TABLE statement:
mysql> ALTER TABLE insect
-> DROP id,
-> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST;
MySQL permits multiple actions to be done with ALTER TABLE (something not true for
all database systems). However, notice that this multiple-action statement is not simply
a concatenation of the two single-action ALTER TABLE statements. The difference is that
it is unnecessary to reestablish the PRIMARY KEY : MySQL doesn't drop it unless the in‐
dexed column is missing after all the actions specified in the ALTER TABLE statement
have been performed.