Database Redesign - Database Processing: Fundamentals, Design, and Implementation - page 378

the asterisk (*) wild card. However, if the view references the columns by name, and if any col-
umn name has been changed, then more work will be needed to revise the view.
Adding and Dropping Columns
Adding null columns to a table is straightforward. For example, to add the null column
DateCreated to WORK, we simply use the ALTER TABLE statement as follows:
/* *** SQL-ALTER-TABLE-CH08-03 *** */
ALTER TABLE WORK
ADD DateCreated Date NULL;
If there are other column constraints, such as DEFAULT or UNIQUE, include them with
the column definition, just as you would if the column definition were part of a CREATE
TABLE statement. However, if you include a DEFAULT constraint, be aware that the default
value will be applied to all new rows, but existing rows will have null values.
Suppose, for example, that you want to set the default value of DateCreated to 1/1/1900 to
signify that the value has not yet been entered. In this case, you would use the ALTER TABLE
statement:
/* *** SQL-ALTER-TABLE-CH08-04 *** */
ALTER TABLE WORK
ADD DateCreated Date NULL DEFAULT '01/01/1900';
This statement causes DateCreated for new rows in WORK to be set to 1/1/1900 by de-
fault. To set existing rows, you would need to execute the following query:
/* *** SQL-UPDATE-CH08-01 *** */
UPDATE WORK
SET DateCreated = '01/01/1900'
WHERE DateCreated IS NULL;
Adding NOT NULL Columns
To add a new NOT NULL column, first add the column as NULL. Then use an UPDATE state-
ment like that just shown to give the column a value in all rows. After the update, the following
SQL ALTER TABLE ALTER COLUMN statement can be executed to change DateCreated from
NULL to NOT NULL.
/* *** SQL-ALTER-TABLE-CH08-05 *** */
ALTER TABLE WORK
ALTER COLUMN DateCreated Date NOT NULL;
Note that this statement will fail if DateCreated has not been given values in all rows.
Dropping Columns
Dropping nonkey columns is easy. For example, eliminating the DateCreated column from
WORK can be done with the following:
/* *** SQL-ALTER-TABLE-CH08-06 *** */
ALTER TABLE WORK
DROP COLUMN DateCreated;
To drop a foreign key column, the constraint that defines the foreign key must first be
dropped. Making such a change is equivalent to dropping a relationship, and that topic is
discussed later in this chapter.