MySQL – MariaDB – Altering Tables

Docker Compose Tutorial for Beginners

Despite the best planning, you will need occasionally to change the structure or other
aspects of your tables. We cannot imagine everything that we might want to
do with a table, or how the data might look when it’s entered. Altering a
table, though, is not very difficult. Because of these factors, you
shouldn’t worry too much about getting the table structure exactly right
when creating a table. You should see tables as more fluid. Perhaps the term
table structure makes that difficult to accept: the
words table and structure have
such rigid senses to them. To offset these images, perhaps a modified
version of a cliché would be useful to give you a truer sense of the reality
of table structures: they’re not made of stone or wood, but of digital
confines that are easily altered. I suspect that sentence won’t be quoted
much, but it’s a useful perspective.

In this chapter, we will explore the ways to alter tables: how to add
and delete columns, how to change their data types, how to add indexes, and
how to change table and column options. This chapter will also include some
precautions about potential data problems you can cause when altering a
table containing data.

Prudence When Altering Tables

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.

Essential Changes

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.

Dynamic Columns

We just covered ENUM, so let’s digress from ALTER TABLE for a moment to
cover dynamic columns. This is something that is available only in
MariaDB, as of version 5.3. It’s similar to an ENUM column,
but with key/value pairs instead of a plain list of options. That will
initially sound confusing, but it make more sense when we look at some
examples. So let’s create a few tables with dynamic columns.

To make the bird-watchers site more interesting, suppose we’ve
decided to do some surveys of the preferences of bird-watchers. We’ll
ask the members to rate birds they like the most. That will be a simple
start. In time, we might ask them to rate the best places to see birds
in an area, or maybe binocular makers and models they like the best. For
this scenario, let’s create a set of tables.

If you’re not using MariaDB and don’t want to replace MySQL with
it, just read along. If you do have MariaDB installed on your server,
enter the following:

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));

The first table we created here will contain a list of surveys.
The second table is where we’ll put the questions. Because we intend to
do only polls, the choices column will contain the survey
choices. We defined it with a very generic type, BLOB, but
we’ll use it to store a dynamic column. The data type used has to be
able to hold the data that will be given to it when we create the
dynamic column. BLOB can be a good choice for that.

The third table is where we will store the answers to the survey
questions. This time we define a VARCHAR column to hold the dynamic column.
We will link survey_answers to
survey_questions based on the question_id, and
survey_questions to surveys based on the
survey_id.

Now let’s put some data in these tables. If you’re using MariaDB,
enter the following SQL statements to add SQL statements:

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') );

That created two surveys: one with a set of choices about where
the birders like to watch birds; the second with a simple, not
comprehensive set of bird types they prefer. We used COLUMN_CREATE() to create the
enumerated lists of choices: each choice has a key and a value. Thus, in
survey_questions, choice 1 is “forest,” choice 2 is
“shore,” and choice 3 is “backyard.” Starting with MariaDB version
10.0.1, you can give strings for the keys instead of numbers.

Let’s see now how data may be retrieved from a dynamic
column:

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

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

This returns the third choice. We used the COLUMN_GET() function to get the dynamic
column within the column given as the first argument. The second
argument specifies the key to use to get the data. We also included
AS to indicate the type of data type it should use (i.e.,
CHAR) to cast the value it returns.

Now let’s enter a bunch of answers for our members. If you’re
using an electronic version of this book, just copy and paste the
following into your MariaDB server:

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);

This isn’t many rows, but it’s enough for now. Let’s count the
votes for the first survey question by executing the following:

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 the WHERE clause, survey_id chose the
survey we want from survey_questions while
question_id chose the question we want from
survey_answers. We retrieve all the answers, group them,
and count the rows for each answer to see how many bird-watchers voted
for each one.

That’s not much data, though. I’ll add more answers to give us a
larger table with which to work. You can download the table from my site. We’ll use it in
examples later in this book. Dynamic columns are still new and very much
under development, so this brief a review will suffice for now. Let’s
now get back to more standard table-related topics.

Optional Changes

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.

Setting a Column’s Default Value

You may have noticed that the results of the DESCRIBE
statements shown in earlier examples have a heading called
Default. You may have also noticed that almost all
of the fields have a default value of NULL. This means that when the
user does not enter a value for the column, the value of NULL will be
used. If you would like to specify a default value for a column, though,
you could have done so when creating the table. For an existing table,
you can use the ALTER TABLE statement to specify a default
value other than NULL. This won’t change the values of existing rows—not
even ones that previously used a default value. You would use either the CHANGE clause or the
ALTER clause. Let’s look at an example of using the
CHANGE clause first.

Suppose that most of the birds that we will list in our database
would have a value of Lower Risk – Least Concern in
the endangered column. Rather than enter Lower
Risk – Least Concern
or its numeric equivalent in each
INSERT statement (which inserts data into a table), we
could change the default value of the endangered column.
Let’s do that and change the column from an ENUM to an
INT data type to prepare for the creation of a reference
table for the conservation status of birds. Let’s also make this a
little more interesting by creating the reference table and inserting
all of the data we had enumerated in the settings for the
endangered. We’ll start by entering the following in
mysql to create the reference
table:

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

We named the reference table conservation_status,
which is a better description than endangered. Notice that
we split each status into two columns. A value like Lower Risk
– Least Concern
was meant to indicate the state of
Least Concern in the category Lower
Risk
. So we created two columns for those values. We’ll put
Lower Risk in the
conservation_category column and Least
Concern
in another column called, conservation_category.

Now let’s insert all of the data into this new reference table.
We’ll use the INSERT statement (covered briefly in Chapter 3):

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');

If you find this SQL statement confusing, just enter it and rest
assured we’ll cover such statements in detail in Chapter 6. For now, though, I wanted to show you
a reference table with data in it. Let’s use the SELECT
statement to select all of the rows of data in the table. Enter just the
SQL statement (shown in bold), not the results that follow it:

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          |
+-----------+-----------------------+------------------------+

The first column gets default values, incrementing automatically
as we asked when we created the table, while the other two columns get
the values we specified during our insert.

Notice that we have been prefixing the table name with the
database name (i.e., rookery.conservation_status). That’s
because we had set the default database to test with
USE. Going back to the birds_new table, we’re
ready to change the endangered column. We decided earlier
that we wanted to set the default value of this column to
Lower Risk – Least Concern, or rather to the value
of the status_id for that combination of columns in the
conservation_status table. Looking at the results, you can
see that the value for the status_id we want for the
default is 8. We can change the endangered column’s name
and default value by entering the following on the server:

ALTER TABLE birds_new
CHANGE COLUMN endangered conservation_status_id INT DEFAULT 8;

The syntax of this is mostly the same as previous examples in this
chapter that use the CHANGE clause (i.e., list the name of
the column twice and restate the data types, even if you don’t want to
change them). The difference in this case is that we’ve added the
keyword DEFAULT followed by the default value—if the
default value were a string, you would put it within quotes. The example
also changed the column name. But if we wanted only to set the default
value for a column, we could use the ALTER clause of the
ALTER TABLE statement. Let’s change the default of
conservation_status_id to 7:

ALTER TABLE birds_new
ALTER conservation_status_id SET DEFAULT 7;

This is much simpler. It only sets the default value for the
column. Notice that the second line starts with ALTER and
not CHANGE. It’s then followed by the column name, and the
SET subclause. Let’s see how that column looks now, running
the SHOW COLUMNS statement only for that column:

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:

As you can see, the default value is now 7.
If we change our minds about having a default value for
conservation_status_id, we would enter the following to
reset it back to NULL, or whatever the initial default value would be
based on the data type of the column:

ALTER TABLE birds_new
ALTER conservation_status_id DROP DEFAULT;

This particular usage of the DROP keyword doesn’t
delete data in the columns. It just alters the column settings so
there is no default value. Run the SHOW COLUMNS statement
again on your computer to see that the default has been reset. Then put
the default back to 7.

Setting the Value of AUTO_INCREMENT

Many of the main tables in a database will have a primary key that
uses the AUTO_INCREMENT option. That creates an
AUTO_INCREMENT variable in the table called
tables in the information_schema database. You
may recognize that database name. We saw the
information_schema database in the results of the
SHOW DATABASE statement in Starting to Explore Databases. When you create a table, MySQL
adds a row to the table called tables in the
information_schema database. One of the columns of that
table is called auto_increment. That is where you can find
the value of the next row to be created in a table. This is initially
set to a value of 1, unless you set it to a different number when
creating the table. Let’s run a SELECT statement to get
that value from the information_schema database, from the
tables table:

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

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

Because we entered data for only six birds in the
birds table, and the value of AUTO_INCREMENT
was not set when the table was created, it started at 1 and now has a
value of 7. That means the next row we add to the
table will have 7 in the column.

If you would like to change the value of
AUTO_INCREMENT for a particular table, you can do so with
the ALTER TABLE statement. Let’s set the value of
AUTO_INCREMENT for the birds table to 10, just
to see how to change it this way. While we’re at it, let’s switch the
default database back to rookery. Enter the
following in mysql:

USE rookery

ALTER TABLE birds
AUTO_INCREMENT = 10;

This will cause the bird_id to be set to 10 for the
next row of data on a bird that we enter into the birds
table. Changing the auto-increment value is not usually necessary, but
it’s good to know that you can do even this with ALTER
TABLE
.

Another Method to Alter and Create a Table

There may be times when you realize that you’ve created a table that is too wide, with
too many columns. Perhaps some columns would be handled better in a
separate table. Or perhaps you started adding new columns to an existing
table and found it became unruly over time. In either case, you could
create a smaller table and then move data from the larger table into the
new, smaller one. To do this, you can create a new table with the same
settings for the columns you want to move, then copy the data from the
first table to the new table, and then delete the columns you no longer
need from the first table. If you wanted to make this transition by the
method just described, the individual column settings will need to be
same in the new table to prevent problems or loss of data.

An easier method for creating a table based on another table is to use the CREATE
TABLE
with the LIKE clause. Let’s try that to create
a copy of the birds table. Enter the following in mysql on your server:

CREATE TABLE birds_new LIKE birds;

This creates an identical table like the birds table,
but with the name birds_new. If you enter the SHOW
TABLES
statement in mysql, you
will see that you now have a birds table and a new table,
birds_new.

Note

You can use an underscore (i.e., _) in a table name, but you may want to avoid using hyphens.
MySQL interprets a hyphen as a minus sign and tries to do a
calculation between the two words given, which causes an error. If you
want to use a hyphen, you must always reference the table name within
quotes.

Execute the following three SQL statements to see what you now
have:

DESCRIBE birds;

DESCRIBE birds_new;

SELECT * FROM birds_new;
Empty set (0.00 sec)

The first two SQL statements will show you the structure of both
tables. They will confirm that they are identical except for their
names. To save space, I didn’t include the results of those two SQL
statements here.

The third SQL statement should show you all of the rows of data in
the birds_new table. Because we copied only the structure
of the birds table when we created the new table, there is
no data—as indicated by the message returned. We could copy the data
over when we’re finished altering the table if that’s what we want to
do.

This method can also be used when making major modifications to a
table. In such a situation, it’s good to work from a copy of the table.
You would then use the ALTER TABLE statement to change the
new table (e.g., birds_new). When you’re finished making
the changes, you would then copy all of the data from the old table to
the new table, delete the original table, and then rename the new
table.

In such a situation, you may have one minor problem. I said
earlier that the tables are identical except for the table names, but
that’s not exactly true. There may be one other difference. If the table
has a column that uses AUTO_INCREMENT for the default
value, the counter will be set to 0 for the new table. You must
determine the current value of AUTO_INCREMENT for the
birds table to be assured that the rows in the new table
have the correct identification numbers. Enter the following SQL statement in mysql:

SHOW CREATE TABLE birds \G

In the results, which are not shown, the last line will reveal the
current value of the AUTO_INCREMENT variable. For instance,
the last line may look as follows:

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

In this excerpt of the results, you can see that the variable,
AUTO_INCREMENT is currently 6. Set
AUTO_INCREMENT to the same value in the
birds_new table by entering the following SQL statement in
mysql:

ALTER TABLE birds_new
AUTO_INCREMENT = 6;

When you’re ready to copy the data from one table to the other,
you can use the INSERT...SELECT syntax. This is covered in
Other Possibilities.

Instead of copying the data after you’re finished modifying the
new table, you can copy the data while creating the new table. This
might be useful when you want to move only certain columns with their
data to a new table, without any alterations to the columns. To do this,
you would still use the CREATE TABLE statement, but with a
slightly different syntax.

Let’s suppose that we have decided that we want to create a new
table for details about each bird (e.g., migratory patterns, habitats,
etc.). Looking at the birds table, though, we decide that
the description column and its data belong in this new
table. So we’ll create a new table and copy that column’s settings and
data, as well as the bird_id into the new table. We can do
that by entering the following from mysql to get the table started:

CREATE TABLE birds_details
SELECT bird_id, description
FROM birds;

This creates the birds_details table with two
columns, based on the same columns in the birds table. It
also copies the data from the two columns in the birds
table into the birds_details table. There is one minor, but
necessary, difference in one of the columns in the new table. The
difference has to do with AUTO_INCREMENT again, but not in
the same way as earlier examples. Enter the DESCRIBE
statement to see the difference:

DESCRIBE birds_details;

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

The difference here is that the bird_id does not use
AUTO_INCREMENT. This is good because we have to manually
set the value of the bird_id for each row that we enter. We
won’t have details for each bird, though, and we won’t necessarily be
entering them in the same order as we will in the birds
table. We could change the bird_id column in this table to
an AUTO_INCREMENT column, but that would cause
problems—trying to keep it in line with the birds table
would be maddening. We could, however, make an index for the
bird_id column in the birds_details table by
using the ALTER TABLE statement and setting the column to a
UNIQUE key. That would allow only one entry per bird, which
may be a good idea. This is covered in Indexes.

The CREATE TABLE...SELECT statement created the birds_details table with only two
columns. We said, though, that we want more columns for keeping other
information on birds. We’ll add those additional columns later with the
ALTER TABLE statement, in the exercises at the end of the
chapter. For now, let’s remove the column description from
the birds table by entering this from mysql:

ALTER TABLE birds
DROP COLUMN description;

This will delete the column and the data in that column. So be
careful using it. This clause will be covered in more depth in Chapter 6.

Renaming a Table

Earlier sections covered how to make changes to the columns in a table.
This included renaming columns. Sometimes, though, you may want to
rename a table. You may do this for style reasons or to change the name
of a table to something more explanatory. You may do it as a method of
replacing an existing table, by deleting the existing table first and
then renaming the replacement table to the deleted table’s name. This is
the situation in some of the examples in the previous section.

We created a copy of the birds table that we called
birds_new in the test database. Our plan was
to modify the birds_new table, then to delete the
birds table from the rookery database and
replace it with birds_new table from the test
database. To fully replace the birds table, in this case,
we will rename birds_new to birds. This is not
done through the ALTER TABLE statement. That’s used only
for altering the structure of columns in a table, not for renaming a
table. Instead, we will use the RENAME TABLE statement. Let’s wait before
doing that. For now, a generic example follows of how you would rename a
table. Do not enter this statement, though:

RENAME TABLE table1_altered
TO table1;

This SQL statement would rename the table1_altered
table to table1. This assumes that a table named
table1 doesn’t already exist in the database. If it does,
it won’t overwrite that table. Instead, you’ll get an error message and
the table won’t be renamed.

The RENAME TABLE statement can also be used to move a
table to another database. This can be useful when you
have a table that you’ve created in one database, as we did in the
test database, and now want to relocate it to a different
database. Because you can both rename and relocate a table in the same
RENAME TABLE statement, let’s do that with our example
instead of using the previous syntax. (Incidentally, relocating a table
without renaming it is also allowed. You would give the name of the new
database, with the same table name.) In our examples, we will have to
either delete or rename the unaltered table in the rookery
database first. Renaming the table that’s being replaced is a safer
choice, so we’ll go with that option.

Let’s rename the birds table in the
rookery database to birds_old and then rename
and relocate the birds_new table from the test
database to birds in the rookery database. To
do all of this in one SQL statement, enter the following:

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

If there was a problem in doing any of these changes, an error
message would be generated and none of the changes would be made. If all
of it went well, though, we should have two tables in the
rookery database that are designed to hold data on
birds.

Let’s run the SHOW TABLES statement to see the tables
in the rookery database. We’ll request only tables starting
with the word birds by using the LIKE
clause with the wildcard, %. Enter the following in mysql:

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          |
+----------------------------+

The birds table used to be the birds_new
table that we altered in the test database. The original
birds table has been renamed to birds_old. The
other tables in the results set here are the ones we created earlier in
this chapter. Because their names start with birds,
they’re in the results. After running a SELECT statement to
ensure that you haven’t lost any data, you might want to delete the
birds_old table. You would delete the
birds_old table with the DROP TABLE statement in mysql. It would look like the following, but
don’t enter this:

DROP TABLE birds_old;

Reordering a Table

The SELECT statement, which is used to retrieve data from a table, has an ORDER BY
clause that may be used to sort or order the results of the statement.
This is useful when displaying data, especially when viewing a table
with many rows of data. Although it’s not necessary, there may be times
in which it would be desirable to resort the data within a table. You
might do this with tables in which the data is rarely changed, such as a
reference table. It can sometimes make a sequential search of the table
faster, but a good index will work fine and is usually better.

As an example of how to reorder a table, if you go to my website,
you will find a table listing country codes. We might use such a table
in conjunction with members of the site or maybe to have a list of birds
spotted in each country. The country_codes table contains
two-character country codes, along with the names of the countries.
Rather than type the name of the country for each record in a related
table for members or bird spottings, we could enter a two-character code
for the country (e.g., us for United States of
America
). The table is already in alphabetical order by name,
but you might want to reorder that table to put rows in alphabetical
order. Or perhaps you want to add a new country to the list, perhaps a
disputed territory that you want to recognize. You might want to reorder
the list after making the addition.

First, let’s see how the data in the table looks now. Let’s enter
the following SELECT statement in mysql, limiting the results to the first five
rows of data:

SELECT * FROM country_codes
LIMIT 5;

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

As you can see, the data is already in alphabetical order based on
the values in the country_name column. Let’s use the
ALTER TABLE statement with its ORDER BY clause
to reorder the data in the table based on the
country_code column. We would probably not want the table
in this order, but let’s do it just to experiment with this clause of
the ALTER TABLE statement. We can change it back
afterwards. Enter the following in mysql:

ALTER TABLE country_codes
ORDER BY country_code;

That should have been processed quickly. Let’s run the
SELECT statement again to see what the first five rows in
the table now contain:

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  |
+--------------+----------------------+

Notice that the results are different and that the rows are now
sorted on the country_code columns without having to
specify that order in the SELECT statement. To put the rows
back in order by country_name, enter the ALTER
TABLE
statement, but with the country_name column
instead of the country_code column.

Again, reordering a table is rarely necessary. You can order the
results of a SELECT statement with the ORDER
BY
clause like so:

SELECT * FROM country_codes
ORDER BY country_name
LIMIT 5;

The results of this SQL statement are the same as the previous
SELECT statement, and the difference in speed is usually
indiscernible.

Indexes

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.

Summary

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.

Exercises

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.

  1. Earlier in this chapter, we created a table called
    birds_details. We created the table with two columns:
    bird_id and description. We took these two
    columns from the birds table. Our intention in creating
    this table was to add columns to store a description of each bird,
    notes about migratory patterns, areas in which they can be found, and
    other information helpful in locating each bird in the wild. Let’s add
    a couple of columns for capturing some of that information.

    Using the ALTER TABLE statement, alter the
    birds_details table. In one SQL statement, add two
    columns named migrate and bird_feeder,
    making them both integer ( INT) columns. These will
    contain values of 1 or 0 (i.e., Yes or
    No). In the same SQL statement, using the
    CHANGE COLUMN clause, change the name of the column,
    description to bird_description.

    When you’re finished altering the table, run the SHOW
    CREATE TABLE
    statement for this table to see the
    results.

  2. Using the CREATE TABLE statement, create a new
    reference table named, habitat_codes. Create this table
    with two columns: name the first column habitat_id and
    make it a primary key using AUTO_INCREMENT and the column
    type of INT. Name the second column habitat
    and use the data type VARCHAR(25). Enter the following
    SQL statement to add data to the table:

    INSERT INTO habitat_codes (habitat)
    VALUES('Coasts'), ('Deserts'), ('Forests'),
    ('Grasslands'), ('Lakes, Rivers, Ponds'),
    ('Marshes, Swamps'), ('Mountains'), ('Oceans'),
    ('Urban');

    Execute a SELECT statement for the table to confirm
    that the data was entered correctly. It should look like this:

    +------------+----------------------+
    | habitat_id | habitat              |
    +------------+----------------------+
    |          1 | Coasts               |
    |          2 | Deserts              |
    |          3 | Forests              |
    |          4 | Grasslands           |
    |          5 | Lakes, Rivers, Ponds |
    |          6 | Marshes, Swamps      |
    |          7 | Mountains            |
    |          8 | Oceans               |
    |          9 | Urban                |
    +------------+----------------------+

    Create a second table named bird_habitats. Name the
    first column bird_id and the second column
    habitat_id. Set the column type for both of them to
    INT. Don’t make either column an indexed column.

    When you’re finished creating both of these tables, execute the
    DESCRIBE and SHOW CREATE TABLE statements
    for each of the two tables. Notice what information is presented by
    each statement, and familiarize yourself with the structure of each
    table and the components of each column.

    Use the RENAME TABLE statement to rename the
    bird_habitats to birds_habitats (i.e., make
    bird plural). This SQL statement was covered in
    Renaming a Table.

  3. Using the ALTER TABLE statement, add an index based
    on both bird_id and the habitat_id columns
    combined (this was covered in Indexes). Instead of using the
    INDEX keyword, use UNIQUE so that duplicates
    are not allowed. Call the index birds_habitats.

    Execute the SHOW CREATE TABLE statement for this
    table when you’re finished altering it.

    At this point, you should enter some data in the
    birds_habitats table. Execute these two
    SELECT statements, to see what data you have in the
    birds and habitat_codes tables:

    SELECT bird_id, common_name
    FROM birds;
    
    SELECT * FROM habitat_codes;

    The results of the first SELECT statement should
    show you a row for a loon and one for a duck, along with some other
    birds. Both the loon and the duck can be found in lakes, but ducks can
    also be found in marshes. So enter one row for the loon and two rows
    for the duck in the birds_habitats table. Give the value
    of the bird_id for the loon, and the value of
    habitat_id for Lakes, Rivers, Ponds.
    Then enter a row giving the bird_id for the duck, and the
    value again of the habitat_id for lakes. Then enter a
    third row giving again the bird_id for the duck and this
    time the habitat_id for Marshes,
    Swamps
    . If you created the index properly, you should not
    get an error about duplicate entries. When you’re done, execute the
    SELECT statement to see all of the values of the
    table.

  4. Using the ALTER TABLE statement, change the name of
    the index you created for birds_habitats in the previous
    exercise (this was covered near the end of this chapter). The index is
    now called birds_habitats. Rename it to
    bird_habitat.

  5. Using the ALTER TABLE statement again, add three
    columns to the humans table in the
    birdwatchers database. Use a single ALTER
    TABLE
    statement to add all three of these columns. Add one
    column named country_id to contain two-character codes
    representing the country where each member is located. Add another
    column named membership_type with enumerated values of
    basic and premium. Add a
    third column named membership_expiration with a data type
    of DATE so that we can track when the membership of
    premium members will expire. These members will have special
    privileges on the site and discounts for items that we sell related to
    bird-watching.

Comments are closed.