Before doing any structural changes to a table, especially if it contains
data, you should make a backup of the table to be changed. You should do
this even if you’re making simple changes. You might lose part of the data
if you inadvertently change the column to a different size, and may lose
all of the data contained in a column if you change the column type to one
that’s incompatible (e.g., from a string to a numeric data type).

If you’re altering only one table, you can make a copy of the table
within the same database to use as a backup in case you make a mistake and
want to restore the table to how it was before you started. A better
choice would be to make a copy of the table and then alter the copy. You
may even want to put the copy in the test database and alter
the table there. When you’re finished altering it, you can use it to
replace the original table. We’ll cover this method in more detail later
in this chapter.

The best precaution to take, in addition to working with copies of
tables, would be to use the mysqldump utility
to make a backup of the tables you’re altering or the whole database. This utility is covered in Chapter 14 . However, to make it easier for you,
here is an example of what you should enter from the command line—not from
the mysql client—to make a backup of
the birds table with mysqldump (you’ll need to have read and write
permission for the directory where you’re executing it; it’s set to the
/tmp directory here, but you should change that to a
different directory, perhaps one to which only you have access and the
filesystem mysql user has read and
write permission):

mysqldump --user='russell' -p \
rookery birds > /tmp/birds.sql

As you can see, the username is given on the first line (you would
enter your username instead of mine) within single or double quotes, with
the -p option to tell mysqldump to prompt you for the password. There
are many other mysqldump options, but
for our purposes, these are all that are necessary. Incidentally, this
statement can be entered in one line from the command line, or it can be
entered on multiple lines as shown here by using the back-slash
( \) to let the shell know that more is to follow. On the
second line in the preceding code block, the database name is given,
followed by the table name. The redirect (>) tells the shell to send
the results of the dump to a text file called birds.sql in the /tmp directory.

The previous example makes a backup of just the birds
table. It may be best to make a backup of the whole rookery
database. To do this with mysqldump,
enter the following from the command line:

mysqldump --user='russell' -p \
rookery > rookery.sql

You should definitely do this, because having a backup of the
rookery database will be helpful in case you
accidentally delete one of the tables or its data and then get confused
later when you’re working on the exercises in later chapters. In fact,
it’s a good idea to make a backup of the rookery database at
the end of each chapter. Each dump file should be named according to its
chapter name (e.g., rookery-ch1-end.sql, rookery-ch2-end.sql, etc.) so that you can rewind
to a specific point in the book.

Later on, if you have a problem and need to restore the database back to where you were at the end of a chapter, you
would enter something like the following from the command line:

mysql --user='russell' -p \
rookery < rookery-ch2-end.sql

Notice that this line does not use the mysqldump utility. We have to use the mysql client at the command line to restore a dump file. When the dump file
( rookery-ch2-end.sql) is read into the
database, it will delete the rookery database with its tables
and data before restoring the back up copy with its tables and data. Any
data that users entered in the interim into the rookery
database will be lost. Notice that to restore from the dump file, we’re
using a different redirect, the less-than sign ( <) to tell
mysql to take input from the contents
of the text file, rookery-ch2-end.sql. It’s possible to restore
only a table or to set other limits on what is restored from a back up
file. You can read about how to do that in Chapter 14. Let’s move on to learning the essentials
of altering tables in MySQL and MariaDB.

After you have created a table, entered data into it, and begun to use it, you will
invariably need to make changes to the table. You may need to add another
column, change the data type of the column (e.g., to allow for more
characters), or perhaps rename a column for clarity of purpose or to align
the columns better with columns in other tables. To improve the speed at
which data is located in the column (i.e., make queries faster), you might
want to add or change an index. You may want to change one of the default
values or set one of the options. All of these changes can be made through
the ALTER TABLE statement.

The basic syntax for the ALTER TABLE is simple:

ALTER TABLE table_name changes;

Replace table_name with the name of the
table you want to change. Enter the changes you want to make on the rest
of the line. We’ll cover the various changes possible with the ALTER
TABLE statement one at a time in this chapter.

This SQL statement starts simply. It’s the specifics of the changes
that can make it confusing. Actually, that isn’t always the reason for the
confusion. The reason many developers have trouble with the ALTER
TABLE statement is because they most likely don’t use it often.
When you need to make a change to a table, you will probably look in a
book or in the documentation to see how to make a change, enter it on your
server, and then forget what you did. In contrast, because you will
frequently use the SQL statements for entering and retrieving data
(i.e., INSERT and SELECT), their syntax will be
easier to remember. So it’s natural that database developers don’t always
remember how to make some of the changes possible with the ALTER
TABLE statement.

One of the most common alterations you will need to make to a table is adding a column. To do this,
include the ADD COLUMN clause as the
changes at the end of the syntax shown earlier.
As an example of this clause, let’s add a column to the
bird_families table to be able to join it to the
bird_orders table. You should have created these two tables
in Chapter 4. We’ll name the column
order_id, the same as in the bird_orders table.
It’s acceptable and perhaps beneficial for it to have the same name as the
related column in the bird_orders table. To do this, enter
the following from the mysql
client:

ALTER TABLE bird_families
ADD COLUMN order_id INT;

This is pretty simple. It adds a column to the table with the name
order_id. It will contain integers, but it will not increment
automatically like its counterpart in the bird_orders table.
You don’t want automatic increments for the column being added to
bird_families, because you’re just referring to existing
orders, not adding new ones.

As another example of this clause, let’s add a couple of columns to
the birds table to be able to join it to the two tables you
should have created in the exercises at the end of Chapter 4 (i.e., birds_wing_shapes and
birds_body_shapes). Before we do that, let’s make a copy of
the table and alter the copy instead of the original. When we’re finished,
we’ll use the table we altered to replace the original table.

To make a copy of the birds table, we’ll use the CREATE TABLE statement with the
LIKE clause. This was covered in Chapter 4) In fact, let’s create the new table in
the test database just to work separately on it (this isn’t
necessary, but it’s a good practice to have a development database
separate from the live one. To do this, enter the following in mysql on your server:

CREATE TABLE test.birds_new LIKE birds;

Next, enter the following two lines in mysql to switch the default database of the
client and to see how the new table looks:

USE test

DESCRIBE birds_new;

This DESCRIBE statement will show you the structure of the new table. Because we
copied only the structure of the birds table when we created
the new table, there is no data in this table. To do that, we could use an
INSERT statement coupled with a SELECT like so:

INSERT INTO birds_new
SELECT * FROM rookery.birds;

This will work fine. However, there’s another method that creates a
table based on another table and copies over the data in the
process:

CREATE TABLE birds_new_alternative
SELECT * FROM rookery.birds;

This will create the table birds_new_alternative with
the data stored in it. However, if you execute a DESCRIBE
statement for the table, you will see that it did not set the
bird_id column to a PRIMARY KEY and did not set
it to AUTO_INCREMENT. So in our situation, the first method
we used to create the table is preferred, followed by an INSERT
INTO...SELECT statement. Enter the following to delete the
alternative table:

DROP TABLE birds_new_alternative;

Be careful with the DROP TABLE statement. Once you delete a table, there is usually no way (or at
least no easy way) to get it back, unless you have a backup copy of the
database. That’s why I suggested that you make a backup at the beginning
of this chapter.

Let’s now alter the new table and add a column named
wing_id to be able to join the table to the
birds_wing_shapes table. To add the column, enter the
following SQL statement in mysql:

ALTER TABLE birds_new
ADD COLUMN wing_id CHAR(2);

This will add a column named wing_id to the table with
a fixed character data type and a maximum width of two characters. I have
made sure to give the column the exact same data type and size as the
corresponding column in birds_wing_shapes, because that
enables us to refer to the column in each table to join the tables.

Let’s look at the structure of the birds_new table to
see how it looks now. Enter the following in your mysql client:

DESCRIBE birds_new;

+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| bird_id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| scientific_name | varchar(100) | YES  | UNI | NULL    |                |
| common_name     | varchar(50)  | YES  |     | NULL    |                |
| family_id       | int(11)      | YES  |     | NULL    |                |
| description     | text         | YES  |     | NULL    |                |
| wing_id         | char(2)      | YES  |     | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+

Looking over the results set for the table, you should recognize the
first six columns. They’re based on the birds table that we
created in Chapter 4. The only change is the
addition we just made. Notice that the new column, wing_id,
was added to the end of the table. Where a column is located matters
little to MySQL or MariaDB. However, it may matter to you as a developer,
especially when working with wider tables or with tables that have many
columns. Let’s try adding this column again, but this time tell MySQL to
put it after the family_id. First, we’ll delete the column we
just added. Because it’s a new column, we can do this without losing
data.

ALTER TABLE birds_new
DROP COLUMN wing_id;

This was even simpler than adding the column. Notice that we don’t mention the column data type or
other options. The command doesn’t need to know that in order to drop a
column. The DROP COLUMN clause removes the column and all of
the data contained in the column from the table. There’s no
UNDO statement in MySQL or in MariaDB, so be careful when
working with a live table.

Let’s add the wing_id column again:

ALTER TABLE birds_new
ADD COLUMN wing_id CHAR(2) AFTER family_id;

This will put the wing_id column after the
family_id in the table. Run the DESCRIBE
statement again to see for yourself. By the way, to add a column to the
first position, you would use the keyword FIRST instead of AFTER.
FIRST takes no column name.

With the ADD COLUMN clause of the ALTER
TABLE statement, we can add more than one column at a time and
specify where each should go. Let’s add three more columns to the
birds_new table. We’ll add columns to join the table to the
birds_body_shapes and birds_bill_shapes tables
we created in the exercises at the end of Chapter 4. We’ll also add a field to note whether a
bird is an endangered species. While we’re making changes, let’s change
the width of the common_name column. It’s only 50 characters
wide now. That may not be enough for some birds that have lengthy common
names. For that change, we’ll use the CHANGE COLUMN clause. Enter the following
in mysql:

ALTER TABLE birds_new
ADD COLUMN body_id CHAR(2) AFTER wing_id,
ADD COLUMN bill_id CHAR(2) AFTER body_id,
ADD COLUMN endangered BIT DEFAULT b'1' AFTER bill_id,
CHANGE COLUMN common_name common_name VARCHAR(255);

This is similar to the previous ALTER TABLE examples
using the ADD COLUMN clause. There are a few differences to
note. First, we entered the ADD COLUMN clause three times,
separated by commas. You might think you should be able to specify the
ADD COLUMN keywords once, and then have each column addition
listed after it, separated by commas. This is a common mistake that even
experienced developers make. You can include multiple clauses in
ALTER TABLE, but each clause must specify just one column.
This restriction may seem unnecessary, but altering a table can cause
problems if you enter something incorrectly. Being emphatic like this is a
good precaution.

In one of the columns added here, the endangered
column, we’re using a data type we haven’t used yet in this book:
BIT. This stores one bit, which takes a values of either set or
unset—basically, 1 or 0. We’ll use this to indicate whether a species is
endangered or not. Notice that we specified a default value for this
column with the DEFAULT keyword followed by the default value. Notice also that to set the
bit, we put the letter b in front of the value in quotes.
There is one quirk—a bug with this data type. It stores the bit fine, but
it does not display the value. If the value is unset (o), it shows a blank
space in the results of a SELECT statement. If the value is
set, it does not show anything, causing the ASCII format of the results
set to be indented by one space to the left. It’s a bug in MySQL that
they’ll resolve eventually—it may even be fixed by the time you read this.
We can still use the data type just fine with this bug. We’ll see this in
action after we finish loading the data into the table.

As for the CHANGE COLUMN clause, notice that we listed
the name of the common_name column twice. The first time is
to name the column that is to be changed. The second time is to provide
the new name, if we wanted to change it. Even though we’re not changing
the name, we still must list it again. Otherwise, it will return an error
message and reject the SQL statement. After the column names, you must
give the data type. Even if you were using the CHANGE COLUMN
statement to change only the name of the column, you must give the data
type again. Basically, when you type CHANGE COLUMN, the
server expects you to fully specify the new column, even if some parts of
the specification remain the same.

There is one more thing to note about the previous ALTER
TABLE example. Notice that we told the server where to locate each
of columns that it’s adding using the AFTER clause. We did
this previously. However, what’s different is that for the second column,
where we’re adding bill_id, we said to locate it after
body_id. You might imagine that would cause an error because
we’re adding the body_id column in the same statement.
However, MySQL executes the clauses of an ALTER TABLE statement in the order that they
are given. Depending on the version and operation, it creates a temporary
copy of the table and alters that copy based on the ALTER
TABLE statement’s instructions, one clause at a time, from left to
right (or top to bottom in our layout). When it’s finished, if there are
no errors, it then replaces the original table with the altered temporary
table—much like we’re doing here, but rapidly and behind the
scenes.

If there are errors in processing any clause of the ALTER
TABLE statement, it just deletes the temporary table and leaves the
original table unchanged, and then returns an error message to the client.
So in the previous example, in the temporary table that MySQL creates, it
first added the column body_id. Once that was done, it then
added the bill_id column and put it after the
body_id column in that temporary table. Your tendency might
have been to have entered AFTER wing_id at the end of each of
the ADD COLUMN clauses. That would have worked, but the
columns would have been in reverse order (i.e., wing_id,
endangered, bill_id, body_id). So
if we want body_id to be located after wing_id,
and bill_id to be located after body_id, and so
on, we have to say so in the SQL statement as shown.

Let’s change now the value of the endangered column.
The table only has five rows in it at the moment and none of the birds
they represent are endangered. Still, let’s set the value of the
endangered column to 0 for four of them. To do this, we
use the UPDATE statement (you’ll learn more
about it in Chapter 8, so don’t worry if this
is unfamiliar):

UPDATE birds_new SET endangered = 0
WHERE bird_id IN(1,2,4,5);

This will set the value of the endangered column to 0,
or rather unset it, for the rows in which the bird_id column
has one of the values listed within the parentheses. Basically, we’ll
change four rows of data, but leave the one unchanged where
bird_id equals 3. Remember that when we created the
endangered column, we gave a default of b'1',
meaning the bit is set by default. The preceding statement is unsetting
that column for the four rows identified in the WHERE
clause.

Now we’ll retrieve data using the SELECT statement (covered in Chapters 3 and 7), based
on whether the endangered column is set. Because the
birds_new table is now wider, we’ll enter the following SQL
statement using the \G for an easier-to-read display:

SELECT bird_id, scientific_name, common_name
FROM birds_new
WHERE endangered \G

*************************** 1. row ***************************
        bird_id: 3
scientific_name: Aix sponsa
    common_name: Wood Duck

*************************** 2. row ***************************
        bird_id: 6
scientific_name: Apteryx mantelli
    common_name: North Island Brown Kiwi

Notice that in the WHERE clause of the
SELECT statement we are selecting rows where the
endangered column has a value. For the column data type of
BIT, this is all that’s needed, and it has the same effect as
if we specified WHERE endangered = 1. To filter on the
reverse—to select rows in which the bit for the endangered
column is not set—use the NOT operator like so:

SELECT * FROM birds_new
WHERE NOT endangered \G

After looking over the display for the Wood Duck and that Kiwi bird,
maybe we should allow for other values for the endangered
column. There are several degrees of endangerment for birds. We could and
should create a separate reference table for the possibilities, but let’s
just enumerate the choices in the column attributes so you can see how
that’s done. While we’re at it, we’ll also relocate the column to just
after the family_id column. For this, we’ll use a new
clause, MODIFY COLUMN:

ALTER TABLE birds_new
MODIFY COLUMN endangered
ENUM('Extinct',
     'Extinct in Wild',
     'Threatened - Critically Endangered',
     'Threatened - Endangered',
     'Threatened - Vulnerable',
     'Lower Risk - Conservation Dependent',
     'Lower Risk - Near Threatened',
     'Lower Risk - Least Concern')
AFTER family_id;

Notice that the syntax for the MODIFY COLUMN clause
lists the name of the column once. That’s because the clause does not
allow you to change the column name. For that, you must use the CHANGE
COLUMN clause. Notice also that we used a new column data type that
lets us enumerate a list of acceptable values: the ENUM data type. The values are enclosed in
quotes, separated by commas, and the set is contained within a pair of
parentheses.

Let’s run the SHOW COLUMNS statement with the LIKE clause to see just the column
settings for the endangered column:

SHOW COLUMNS FROM birds_new LIKE 'endangered' \G

*************************** 1. row ***************************
  Field: endangered
   Type: enum('Extinct','Extinct in Wild',
              'Threatened - Critically Endangered',
              'Threatened - Endangered',
              'Threatened - Vulnerable',
              'Lower Risk - Conservation Dependent',
              'Lower Risk - Near Threatened',
              'Lower Risk - Least Concern')
   Null: YES
    Key:
Default: NULL
  Extra: 

In addition to the values enumerated, notice that a NULL value is allowed and is the default. We could have
disallowed NULL values by including a NOT NULL clause.

If we want to add another value to the enumerated list, we would use
the ALTER TABLE statement again with the MODIFY
COLUMN clause, without the AFTER clause
extension—unless we want to relocate the column again. We would have to
list all of the enumerated values again, with the addition of the new
one.

To set the values in a column that has an enumerated list, you can
either give a value shown in the list, or refer to the value numerically,
if you know the order of the values. The first enumerated value would be
1. For instance, you could do an UPDATE statement like this to set all birds in the table to Lower
Risk – Least Concern, the seventh value:

UPDATE birds_new
SET endangered = 7;

I said earlier that using the ENUM data type can be an
alternative to a reference table when there are a few values. However, the
endangered column as shown in this example is cumbersome and
not professional. We could still do a reference table in addition to this
enumerated list within the table. The reference table would have a row for
each of these choices, but with extra columns that would provide more
information for them, for when we wanted to display more information.
Based on that, we could change the values in the enumerated list in the
birds table to something easier to type (e.g.,
LR-LC for Lower Risk – Least
Concern) and then put the lengthier description in the
reference table that we’d create.

It will be simpler, however, to treat the endangered column like the
other reference tables that we’ve created (e.g.,
birds_wing_shapes) and use numbers for the values in the
birds table. We should change the column and create another
reference table for it. We’ll do that later, though.

USE birdwatchers;

CREATE TABLE surveys
(survey_id INT AUTO_INCREMENT KEY,
survey_name VARCHAR(255));

CREATE TABLE survey_questions
(question_id INT AUTO_INCREMENT KEY,
survey_id INT,
question VARCHAR(255),
choices BLOB);

CREATE TABLE survey_answers
(answer_id INT AUTO_INCREMENT KEY,
human_id INT,
question_id INT,
date_answered DATETIME,
answer VARCHAR(255));

INSERT INTO surveys (survey_name)
VALUES("Favorite Birding Location");

INSERT INTO survey_questions
(survey_id, question, choices)
VALUES(LAST_INSERT_ID(),
"What's your favorite setting for bird-watching?",
COLUMN_CREATE('1', 'forest', '2', 'shore', '3', 'backyard') );

INSERT INTO surveys (survey_name)
VALUES("Preferred Birds");

INSERT INTO survey_questions
(survey_id, question, choices)
VALUES(LAST_INSERT_ID(),
"Which type of birds do you like best?",
COLUMN_CREATE('1', 'perching', '2', 'shore', '3', 'fowl', '4', 'rapture') );

SELECT COLUMN_GET(choices, 3 AS CHAR)
AS 'Location'
FROM survey_questions
WHERE survey_id = 1;

+----------+
| Location |
+----------+
| backyard |
+----------+

INSERT INTO survey_answers
(human_id, question_id, date_answered, answer)
VALUES
(29, 1, NOW(), 2),
(29, 2, NOW(), 2),
(35, 1, NOW(), 1),
(35, 2, NOW(), 1),
(26, 1, NOW(), 2),
(26, 2, NOW(), 1),
(27, 1, NOW(), 2),
(27, 2, NOW(), 4),
(16, 1, NOW(), 3),
(3, 1, NOW(), 1),
(3, 2, NOW(), 1);

SELECT IFNULL(COLUMN_GET(choices, answer AS CHAR), 'total')
AS 'Birding Site', COUNT(*) AS 'Votes'
FROM survey_answers
JOIN survey_questions USING(question_id)
WHERE survey_id = 1
AND question_id = 1
GROUP BY answer WITH ROLLUP;

+--------------+-------+
| Birding Site | Votes |
+--------------+-------+
| forest       |     2 |
| shore        |     3 |
| backyard     |     1 |
| total        |     6 |
+--------------+-------+

In addition to the most common uses for the ALTER TABLE
statement (i.e., adding and renaming columns), you can use it to set some
of the options of an existing table and its columns. You can also use the
ALTER TABLE statement to set the value of table variables, as
well as the default value of columns. This section covers how to change
those settings and values, as well as how to rename a table. Additionally,
you can change indexes in a table. That is covered in the section on Indexes.

CREATE TABLE rookery.conservation_status
(status_id INT AUTO_INCREMENT PRIMARY KEY,
conservation_category CHAR(10),
conservation_state CHAR(25) );

INSERT INTO rookery.conservation_status
(conservation_category, conservation_state)
VALUES('Extinct','Extinct'),
('Extinct','Extinct in Wild'),
('Threatened','Critically Endangered'),
('Threatened','Endangered'),
('Threatened','Vulnerable'),
('Lower Risk','Conservation Dependent'),
('Lower Risk','Near Threatened'),
('Lower Risk','Least Concern');

SELECT * FROM rookery.conservation_status;

+-----------+-----------------------+------------------------+
| status_id | conservation_category | conservation_state     |
+-----------+-----------------------+------------------------+
|         1 | Extinct               | Extinct                |
|         2 | Extinct               | Extinct in Wild        |
|         3 | Threatened            | Critically Endangered  |
|         4 | Threatened            | Endangered             |
|         5 | Threatened            | Vulnerable             |
|         6 | Lower Risk            | Conservation Dependent |
|         7 | Lower Risk            | Near Threatened        |
|         8 | Lower Risk            | Least Concern          |
+-----------+-----------------------+------------------------+

ALTER TABLE birds_new
CHANGE COLUMN endangered conservation_status_id INT DEFAULT 8;

ALTER TABLE birds_new
ALTER conservation_status_id SET DEFAULT 7;

SHOW COLUMNS FROM birds_new LIKE 'conservation_status_id' \G

*************************** 1. row ***************************
  Field: conservation_status_id
   Type: int(11)
   Null: YES
    Key:
Default: 7
  Extra:

ALTER TABLE birds_new
ALTER conservation_status_id DROP DEFAULT;

SELECT auto_increment
FROM information_schema.tables
WHERE table_name = 'birds';

+----------------+
| auto_increment |
+----------------+
|              7 |
+----------------+

USE rookery

ALTER TABLE birds
AUTO_INCREMENT = 10;

CREATE TABLE birds_new LIKE birds;

DESCRIBE birds;

DESCRIBE birds_new;

SELECT * FROM birds_new;
Empty set (0.00 sec)

SHOW CREATE TABLE birds \G

...
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 COLLATE=latin1_bin

ALTER TABLE birds_new
AUTO_INCREMENT = 6;

CREATE TABLE birds_details
SELECT bird_id, description
FROM birds;

DESCRIBE birds_details;

+-------------+---------+------+-----+---------+-------+
| Field       | Type    | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| bird_id     | int(11) | NO   |     | 0       |       |
| description | text    | YES  |     | NULL    |       |
+-------------+---------+------+-----+---------+-------+

ALTER TABLE birds
DROP COLUMN description;

RENAME TABLE table1_altered
TO table1;

RENAME TABLE rookery.birds TO rookery.birds_old,
test.birds_new TO rookery.birds;

SHOW TABLES IN rookery LIKE 'birds%';

+----------------------------+
| Tables_in_rookery (birds%) |
+----------------------------+
| birds                      |
| birds_bill_shapes          |
| birds_body_shapes          |
| birds_details              |
| birds_new                  |
| birds_old                  |
| birds_wing_shapes          |
+----------------------------+

DROP TABLE birds_old;

SELECT * FROM country_codes
LIMIT 5;

+--------------+----------------+
| country_code | country_name   |
+--------------+----------------+
| af           | Afghanistan    |
| ax           | Åland Islands  |
| al           | Albania        |
| dz           | Algeria        |
| as           | American Samoa |
+--------------+----------------+

ALTER TABLE country_codes
ORDER BY country_code;

SELECT * FROM
country_codes LIMIT 5;

+--------------+----------------------+
| country_code | country_name         |
+--------------+----------------------+
| ac           | Ascension Island     |
| ad           | Andorra              |
| ae           | United Arab Emirates |
| af           | Afghanistan          |
| ag           | Antigua and Barbuda  |
+--------------+----------------------+

SELECT * FROM country_codes
ORDER BY country_name
LIMIT 5;

One of the most irritating 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
conservation_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 book. For example, if you want to find
the syntax for the ALTER TABLE statement, you could start at
the beginning of this book and flip through the pages rapidly and
sequentially—assuming you have a print version of this book—until you spot
those keywords. That would be searching for data without an index.
Instead, you could flip to the beginning of the book 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 book, 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, let’s look at the
index for the humans table we created in Chapter 4, by using the SHOW INDEX statement. Enter the following
from mysql:

SHOW INDEX FROM birdwatchers.humans \G

*************************** 1. row ***************************
       Table: humans
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 1
 Column_name: human_id
   Collation: A
 Cardinality: 0
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:

The output shows that behind the scenes there is an index associated
with the human_id (look in the preceding output where it
says, Column_name). The human_id column
is not the index, but the data from which the index is drawn. The name of
the column and name of the index are the same and the index is bound to
the column, but they are not the same. Let’s alter this table and add
another index to make this clearer.

Suppose that users of the humans table sometimes search
based on the last name of the member. Without an index, MySQL will search
the last_name column sequentially. Let’s confirm that by
using the EXPLAIN statement, coupled with the
SELECT statement. This will return information on how the
SELECT statement searches the table and on what basis. It
will explain what the server did when executing the SELECT
statement—so it won’t return any rows from the table, but information on
how the index would be used had you executed only the SELECT
statement. Enter the following in mysql:

EXPLAIN SELECT * FROM birdwatchers.humans
WHERE name_last = 'Hollar' \G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: humans
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Using where

The EXPLAIN statement here analyzes the
SELECT statement given, which is selecting all of the columns
in the humans table where the value for the
name_last column equals Hollar. What is
of interest to us in the results is the possible_keys field
and the key field—a key is the column on which a table is
indexed. However, the words key and
index are fairly interchangeable. The
possible_keys field would show the keys that the
SELECT statement could have used. In this case, there is no
index related to the name_last column. The key
would list the index that the statement actually used. Again, in this case
there were none, so it shows a value of NULL. There are only four names in
this table, so an index would not make a noticeable difference in
performance. However, if this table might one day have thousands of names,
an index will greatly improve the performance of look-ups on people’s
names.

In addition to sometimes searching the humans table
based on the member’s last name, suppose that users sometimes search based
on the first name, and sometimes based on both the first and last names.
To prepare for those possibilities and to improve performance for a time
when the table will have many records, let’s create an index that combines
the two columns. To do this, we will use the ALTER TABLE statement with the ADD
INDEX clause like so:

ALTER TABLE birdwatchers.humans
ADD INDEX human_names (name_last, name_first);

Now let’s run the SHOW CREATE TABLE statement to see
how the index looks from that perspective:

SHOW CREATE TABLE birdwatchers.humans \G

*************************** 1. row ***************************
       Table: humans
Create Table: CREATE TABLE `humans` (
  `human_id` int(11) NOT NULL AUTO_INCREMENT,
  `formal_title` varchar(25) COLLATE latin1_bin DEFAULT NULL,
  `name_first` varchar(25) COLLATE latin1_bin DEFAULT NULL,
  `name_last` varchar(25) COLLATE latin1_bin DEFAULT NULL,
  `email_address` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  PRIMARY KEY (`human_id`),
  KEY `human_names` (`name_last`,`name_first`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin

The results show a new KEY after the list of columns.
The key, or index, is called human_names and is based on the
values of the two columns listed in parentheses. Let’s use another SQL
statement to see more information about this new index. We’ll use the
SHOW INDEX statement like so:

SHOW INDEX FROM birdwatchers.humans
WHERE Key_name = 'human_names' \G

*************************** 1. row ***************************
       Table: humans
  Non_unique: 1
    Key_name: human_names
Seq_in_index: 1
 Column_name: name_last
   Collation: A
 Cardinality: NULL
    Sub_part: NULL
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment:
*************************** 2. row ***************************
       Table: humans
  Non_unique: 1
    Key_name: human_names
Seq_in_index: 2
 Column_name: name_first
   Collation: A
 Cardinality: NULL
    Sub_part: NULL
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment: 

This SQL statement shows the components of the
human_names index. The results show two rows with information
on the columns that were used to create the index. There’s plenty of
information here about this index. It’s not important that you understand
what it all means at this point in learning MySQL and MariaDB. What I want
you to see here is that the name of the index is different from the
columns upon which it’s based. When there’s only one column in the index
and the index for it has the same name, it doesn’t mean that they are the
same thing.

Let’s try the EXPLAIN...SELECT again to see the difference from earlier when we didn’t have the
human_names index:

EXPLAIN SELECT * FROM birdwatchers.humans
WHERE name_last = 'Hollar' \G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: humans
         type: ref
possible_keys: human_names
          key: human_names
      key_len: 28
          ref: const
         rows: 1
        Extra: Using where

As shown in the results, this time the possible_keys
field indicates that the human_names key could be used. If
there were more than one possible key that could be used, the line would
list them here. In line with the index’s presence in
possible_keys, the key shows that the
human_names index was actually used. Basically, when a
SELECT is run in which the user wants to search the table
based on the person’s last name, MySQL will use the
human_names index that we created, and not search the
name_last column sequentially. That’s what we want. That will
make for a quicker search.

Now that you hopefully have a better understanding of indexes in
general and their relation to columns, let’s go back to the earlier task
of renaming the column in the conservation_status table from
status_id to conservation_status_id. Because the
index is associated with the column, we need to remove that association in
the index. Otherwise, the index will be associated with a column that does
not exist from its perspective: it will be looking for the column by the
old name. So, let’s delete the index and rename the column, and then add a
new index based on the new column name. To do that, enter the following
SQL statement in mysql:

ALTER TABLE conservation_status
DROP PRIMARY KEY,
CHANGE status_id conservation_status_id INT PRIMARY KEY AUTO_INCREMENT;

The clauses must be in the order shown, because the index must be dropped before
the column with which it’s associated can be renamed. Don’t worry about
losing data: the data in the columns is not deleted, only the index, which
will be re-created easily by MySQL. We don’t have to give the name of the
associated column when dropping a PRIMARY KEY. There is and
can be only one primary key.

At this point, you should have a better sense of indexes and the
procedure for changing them with the ALTER TABLE statement.
The order in which you make changes to indexes and the columns on which
they are based matters. Why it matters should be clear now. So that you
can get more practice with these concepts and syntax, though, in one of
the exercises at the end of the chapter you will be asked to change some
columns and indexes. Be sure to complete all of the exercises.

Good planning is certainly key to developing an efficient database.
However, as you can see from all of the examples of how to use the
ALTER TABLE statement, MySQL is malleable enough that a
database and its tables can be reshaped without much trouble. Just be sure
to make a backup before restructuring a database, and work from a copy of
a table before altering it. Check your work and the data when you’re
finished, before committing the changes made.

With all of this in mind, after having had some experience altering
tables in this chapter, you should feel comfortable in creating tables, as
you now know that they don’t have to be perfect from the beginning. You
should also have a good sense of the options available with columns and
how to set them. And you should have a basic understanding of indexes, how
they’re used, and how they may be created and changed.

If you have found this chapter confusing, though, it may be that you
need more experience using tables with data. In the next part of this
book, you will get plenty of experience working with tables, inserting
data into columns, and changing the data. When you see how the data comes
together, you’ll have a better understanding of how to structure a table
and how to set columns in preparation for data. You’ll have a better
appreciation of how multiple tables may be joined together to get the
results you want.

Besides the SQL statements you entered on your MySQL or MariaDB
server while reading this chapter, here are a few practice exercises to
further strengthen what we’ve covered. They’re related to creating and
altering tables. We’ll use these tables with the modifications you’ll make
in later chapters, so make sure to complete all of the exercises
here.