Altering Tables - Learning MySQL and MariaDB - page 74

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 in-
clude 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 topic: BIT . Thisstores 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 theres-
ults 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 fin-
ish loading the data into the table.
As for the CHANGE COLUMN clause, notice that we listed the name of the com-
mon_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 specific-
ation 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 state-
ment. However, MySQL executes the clauses ofan ALTER TABLE statement in the or-
der that they are given. Depending on the version and operation, it creates a temporary