Learning MySQL and MariaDB (2015)

Part II. Database Structures

Chapter 5. Altering Tables

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 therookery 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 thebird_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;

+-----------------+--------------+------+-----+---------+----------------+

+-----------------+--------------+------+-----+---------+----------------+

| bird_id | int(11) | NO | PRI | NULL | auto_increment |

| family_id | int(11) | 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 typeCHANGE 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 columnbody_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, insurvey_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 ofconservation_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;

+-------------+---------+------+-----+---------+-------+

+-------------+---------+------+-----+---------+-------+

| bird_id | int(11) | NO | | 0 | |

+-------------+---------+------+-----+---------+-------+

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 thebirds 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 theALTER 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 thecountry_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 inmysql:

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 COLUMNclause, 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:

3. INSERT INTO habitat_codes (habitat)

4. VALUES('Coasts'), ('Deserts'), ('Forests'),

5. ('Grasslands'), ('Lakes, Rivers, Ponds'),

6. ('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:

4. SELECT bird_id, common_name

5. 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 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.