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

Finally, for organizations that have very large databases, it may not be possible to make a
complete copy of the operational database prior to making structural changes. In this case, the
database is backed up in pieces, and the changes are made in pieces as well. This task is very
difficult and requires great knowledge and expertise. It also requires weeks or months of plan-
ning. You may participate as a junior member of a team to make such a change, but you should
have years of database experience before you attempt to make structural changes to such large
databases. Even then, it is a daunting task.
Changing Table Names and Table Columns
In this section, we will consider alterations to tables and their columns. To accomplish these
changes, we will use only SQL statements. Many DBMS products have features to facilitate
changing structures other than SQL. For example, some products have graphical design tools
that simplify this process. But such features are not standardized, and you should not depend
on them. The statements shown in this chapter will work with any enterprise-class DBMS
product, and most will work with Microsoft Access as well.
Changing Table Names
At first glance, changing a table name seems like an innocent and easy operation. A review of
Figure 8-3, however, shows that the consequences of such a change are greater than you would
think. If, for example, we want to change the name of the table WORK to WORK_VERSION2,
several tasks are necessary. The constraint that defines the relationship from WORK to
TRANS must be altered, ArtistWorkNetView view must be redefined, and then the TRANS_
CheckSalesPrice trigger must be rewritten to use the new name.
Oracle Database and MySQL have an SQL RENAME {Name01} TO {Name02} state-
ment that can be used to rename tables, while SQL Server uses the system stored procedure
sp_rename to accomplish the same task. However, while the table name itself is changed,
other objects that use that table name, such as triggers and stored procedures, will not be
modified! Therefore, these methods of renaming a table are useful only in certain situations.
Instead, we will use the following strategy for making table name changes. First, create the
new table with all attendant structures and then drop the old one once everything is working
with the new table. If the table to be renamed is too large to be copied, other strategies will
have to be used, but they are beyond the scope of this discussion.
This strategy has one serious problem, however. WorkID is a surrogate key. When we create
the new table, the DBMS will create new values of WorkID in the new table. The new values will
not necessarily match the values in the old table, which means values of the foreign key TRANS.
WorkID will be wrong. The easiest way to solve this problem is first to create the new version of
the WORK table and not define WorkID as a surrogate key. Then fill the table with the current
values of WORK, including the current values of WorkID. Then change WorkID to a surrogate key.
First, we create the table by submitting an SQL CREATE TABLE WORK_VERSION2
statement to the DBMS. We make WorkID an integer, but not a surrogate key. We also must
give new names to the WORK constraints. The prior constraints still exist, and if new names
are not used, the DBMS will issue a duplicate constraint error when processing the CREATE
TABLE statements. Examples of new constraint names are:
/* *** EXAMPLE CODE - DO NOT RUN *** */
CONSTRAINT WorkV2PK PRIMARY KEY (WorkID),
CONSTRAINT WorkV2AK1 UNIQUE (Title, Copy),
CONSTRAINT ArtistV2FK FOREIGN KEY(ArtistID)
REFERENCES ARTIST(ArtistID)
ON DELETE NO ACTION
ON UPDATE NO ACTION;