Database Reference
In-Depth Information
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 aNULL value is allowed and is the de-
fault. We could have disallowed NULL values byincluding 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 enumer-
ated 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 toa 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
