Database Reference
In-Depth Information
To drop the primary key, the primary key constraint first needs to be dropped. To drop
that, however, all foreign keys that use the primary key must first be dropped. Thus, to drop the
primary key of WORK and replace it with the composite primary key (Title, Copy, ArtistID),
the following steps are necessary:
●
Drop the constraint WorkFK from TRANS.
●
Drop the constraint WorkPK from WORK.
●
Create a new WorkPK constraint using (Title, Copy, ArtistID).
●
Create a new WorkFK constraint referencing (Title, Copy, ArtistID) in TRANS.
●
Drop the column WorkID.
It is important to verify that all changes have been made correctly before dropping
WorkID. Once it is dropped, there is no way to recover it except by restoring the WORK table
from a backup.
Changing a Column Data Type or Column Constraints
To change a column data type or to change column constraints, the column is redefined using
the ALTER TABLE ALTER COLUMN command. However, if the column is being changed from
NULL to NOT NULL, then all rows must have a value in that column for the change to succeed.
Also, some data type changes may cause data loss. Changing Char(50) to Date, for example,
will cause loss of any text field that the DBMS cannot successfully transform into a date value.
Or, alternatively, the DBMS may simply refuse to make the column change. The results depend
on the DBMS product in use.
Generally, converting numeric to Char or Varchar will succeed. Also, converting Date or
Money or other more specific data types to Char or Varchar will usually succeed. Converting
Char or Varchar back to Date, Money, or Numeric is risky, and it may or may not be possible.
In the View Ridge schema, if DateOfBirth had been defined as Char(4), then a risky but
sensible data type change would be to modify DateOfBirth in the ARTIST table to Numeric(4,0).
This would be a sensible change because all of the values in this column are numeric.
Recall the check constraint that was used to define DateOfBirth (refer to Figure 7-14). The
following makes that change and simplifies the CHECK constraint.
/* *** EXAMPLE CODE - DO NOT RUN *** */
/* *** SQL-ALTER-TABLE-CH08-07 *** */
ALTER TABLE ARTIST
ALTER COLUMN DateOfBirth Numeric(4,0) NULL;
ALTER TABLE ARTIST
ADD CONSTRAINT NumericBirthYearCheck
CHECK (DateOfBirth > 1900 AND DateOfBirth < 2100);
The prior check constraints on DateOfBirth should now be deleted.
Adding and Dropping Constraints
As already shown, constraints can be added and removed using the ALTER TABLE ADD
CONSTRAINT and ALTER TABLE DROP CONSTRAINT statements.
Changing cardinalities is a common database redesign task. Sometimes, the need is to change
minimum cardinalities from zero to one or from one to zero. Another common task is to
change the maximum cardinality from 1:1 to 1:N or from 1:N to N:M. Another possibility, which
is less common, is to decrease maximum cardinality from N:M to 1:N or from 1:N to 1:1. This
latter change can only be made with data loss, as you will see.
Search WWH ::
Custom Search