Database Reference
In-Depth Information
Optional Changes
In addition to the most common uses for the
ALTER TABLE
statement (i.e., adding and re-
naming 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 havenoticed that the results of the
DESCRIBE
statements shown in earlier ex-
amples 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 exist-
ing 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 useeither 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 in-
to 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 inter-
esting 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
