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
statement one at a time in this chapter.
TABLE
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
statement is because they most likely don’t use it often.
TABLE
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
statement.
TABLE
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
statement. Enter the following to delete the
INTO...SELECT
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
statement, we can add more than one column at a time and
TABLE
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
example. Notice that we told the server where to locate each
TABLE
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
statement’s instructions, one clause at a time, from left to
TABLE
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
statement, it just deletes the temporary table and leaves the
TABLE
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
clause. Notice also that we used a new column data type that
COLUMN
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
clause, without the
COLUMN 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
with the
TABLE 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
statement in mysql, you
TABLES
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
statement, but with the
TABLE 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
clause like so:
BY
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
statement is having to use it to change an index. If you try
TABLE
to rename a column that is indexed by using only an ALTER
statement, you will get a frustrating and confusing error message. For
TABLE
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
clause like so:
INDEX
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.
-
Earlier in this chapter, we created a table called
birds_details
. We created the table with two columns:
bird_id
anddescription
. We took these two
columns from thebirds
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 namedmigrate
andbird_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
tobird_description
.When you’re finished altering the table, run the
SHOW
statement for this table to see the
CREATE TABLE
results. -
Using the
CREATE TABLE
statement, create a new
reference table named,habitat_codes
. Create this table
with two columns: name the first columnhabitat_id
and
make it a primary key usingAUTO_INCREMENT
and the column
type ofINT
. Name the second columnhabitat
and use the data typeVARCHAR(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 columnbird_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
andSHOW 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
tobirds_habitats
(i.e., make
bird plural). This SQL statement was covered in
Renaming a Table. -
Using the
ALTER TABLE
statement, add an index based
on bothbird_id
and thehabitat_id
columns
combined (this was covered in Indexes). Instead of using the
INDEX
keyword, useUNIQUE
so that duplicates
are not allowed. Call the indexbirds_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
andhabitat_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 thebirds_habitats
table. Give the value
of thebird_id
for the loon, and the value of
habitat_id
for Lakes, Rivers, Ponds.
Then enter a row giving thebird_id
for the duck, and the
value again of thehabitat_id
for lakes. Then enter a
third row giving again thebird_id
for the duck and this
time thehabitat_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. -
Using the
ALTER TABLE
statement, change the name of
the index you created forbirds_habitats
in the previous
exercise (this was covered near the end of this chapter). The index is
now calledbirds_habitats
. Rename it to
bird_habitat
. -
Using the
ALTER TABLE
statement again, add three
columns to thehumans
table in the
birdwatchers
database. Use a singleALTER
statement to add all three of these columns. Add one
TABLE
column namedcountry_id
to contain two-character codes
representing the country where each member is located. Add another
column namedmembership_type
with enumerated values of
basic and premium. Add a
third column namedmembership_expiration
with a data type
ofDATE
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.