Database Reference
In-Depth Information
Figure 18.19
Describing the
RELEASESIN2001
Table.
Let's add a column to the RELEASESIN2001 table. This column will
contain only the month part of the release date of the CD. See the result in
Figure 18.20.
ALTER TABLE RELEASESIN2001
ADD(RELEASE_MONTH CHAR(10) NOT NULL);
The previous ALTER TABLE command produced an error. The
RELEASE_MONTH column is non-nullable. When you create a new col-
umn in a table that already has rows, the column is created with null values
in all existing rows. We tried to create a column that does not allow null val-
ues (because of the NOT NULL constraint, see Chapter 20). Because there
are rows in the table, the ALTER TABLE statement in Figure 18.20
attempts to add a non-nullable column to a table while placing null values
into that column for all existing rows; this causes the error.
There is, of course, another way to accomplish the task of adding a non-
nullable column to a table. You first add the column, allowing null values.
Then you populate the column with values. And finally, you change the
column to NOT NULL.
We can accomplish all three steps using the following script:
ALTER TABLE RELEASESIN2001 ADD(RELEASE_MONTH CHAR(10));
UPDATE RELEASESIN2001 SET RELEASE_MONTH =
INITCAP(TO_CHAR(RELEASED,'MONTH'));
ALTER TABLE RELEASESIN2001
MODIFY(RELEASE_MONTH CHAR(10) NOT NULL);
