Altering Tables - Learning MySQL and MariaDB - page 84

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 state-
mentonly 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 ;