Database Reference
In-Depth Information
To change a 1:N to a 1:1 relationship, we just need to remove any duplicate values of the
foreign key of the relationship and then add a unique constraint on the foreign key. See Project
Question 8.51.
Adding new tables and relationships is straightforward. Just add the tables and relationships
using CREATE TABLE statements with FOREIGN KEY constraints, as shown before. If an
existing table has a child relationship to the new table, add a FOREIGN KEY constraint using
the existing table.
For example, if a new table, COUNTRY, were added to the View Ridge database with the
primary key Name and if CUSTOMER.Country is to be used as a foreign key in the new table, a
new FOREIGN KEY constraint would be defined in CUSTOMER:
/* *** EXAMPLE CODE - DO NOT RUN *** */
/* *** SQL-ALTER-TABLE-CH08-11 *** */
ALTER TABLE CUSTOMER
ADD CONSTRAINT CountryFK FOREIGN KEY (Country)
REFERENCES COUNTRY(Name)
ON UPDATE CASCADE;
Deleting relationships and tables is just a matter of dropping the foreign key constraints
and then dropping the tables. Of course, before this is done, dependency graphs must be
constructed and used to determine which views, triggers, stored procedures, and application
programs will be affected by the deletions.
As described in Chapter 4, another reason to add new tables and relationships or to com-
press existing tables into fewer tables is for normalization and denormalization. We will not
address that topic further in this chapter, except to say that normalization and denormaliza-
tion are common tasks during database redesign.
You can use a variety of different data modeling products to make database changes on your
behalf. To do so, you first reverse engineer the database, make changes to the RE data model,
and then invoke the forward-engineering functionality of the data modeling tool.
We will not consider forward engineering here because it hides the SQL that you need to
learn. Also, the specifics of the forward-engineering process are product dependent.
Because of the importance of making data model changes correctly, many professionals
are skeptical about using an automated process for database redesign. Certainly, it is neces-
sary to test the results thoroughly before using forward engineering on operational data. Some
products will show the SQL they are about to execute for review before making the changes to
the database.
Database redesign is one area in which automation may not be the best idea. Much de-
pends on the nature of the changes to be made and the quality of the forward-engineering
features of the data modeling product. Given the knowledge you have gained in this chapter,
you should be able to make most redesign changes by writing your own SQL. There is nothing
wrong with that approach!
Search WWH ::
Custom Search