Altering Tables - Learning MySQL and MariaDB - page 93

Indexes
One of the mostirritating tasks for beginners in using the ALTER TABLE statement is
having to use it to change an index. If you try to rename a column that is indexed by using
only an ALTER TABLE statement,you will get a frustrating and confusing error message.
For instance, suppose we decide to rename the primary key column in the conserva-
tion_status table from status_id to conservation_status_id . To do so, we
might try an SQL statement like this:
ALTER TABLE conservation_status
CHANGE status_id conservation_status_id INT AUTO_INCREMENT PRIMARY
KEY;
ERROR 1068: Multiple primary key defined
When you first try doing this, you will probably think that you're remembering the syntax
incorrectly. So you'll try different combinations, but nothing will work. To avoid this and
to get it right the first time, you will need to understand indexes better and understand that
an index is separate from the column upon which the index is based.
Indexes are used by MySQL to locate data quickly. They work very much like the index in
the back of a book. Let's use that metaphor to compare methods of searching this topic. For
example, if you want to find the syntax for the ALTER TABLE statement, you could start
at the beginning of this topic and flip through the pages rapidly and sequentially — assum-
ing you have a print version of this topic — until you spot those keywords. That would be
searching for data without an index. Instead, you could flip to the beginning of the topic
and search the Table of Contents, which is a broader index, for a chapter title using the
words alter table and then search within the chapters containing those words in their title.
That's an example of a simple or poor index. A better choice would be to go to the index at
the back of this topic, look for the list of pages in which ALTER TABLE can be found, and
go straight to those pages to find what you want.
An index in MySQL works similarly to the last example. Without an index, rows are
searched sequentially. Because an index is smaller and is structured to be traversed quickly,
it can be searched rapidly and then MySQL can jump directly to the row that matches the
search pattern. So when you create a table, especially one that will hold many rows of data,
create it with an index. The database will run faster.
With this metaphor of a book index in mind, you can better understand that an index is not
the same as a column, although it is related to columns. To illustrate this in a MySQL table,