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

Next, copy the data into the new table with the following SQL statement:

/* *** EXAMPLE CODE - DO NOT RUN *** */
/* *** SQL-INSERT-CH08-01 *** */
INSERT INTO WORK_VERSION2 (WorkID, Copy, Title, Medium,

Description, ArtistID)

SELECT WorkID, Copy, Title, Medium, Description, ArtistID

FROM WORK;

At this point, alter the WORK_VERSION2 table to make WorkID a surrogate key. In
SQL Server, the easiest way to do that is to open the graphical table designer and redefine
WorkID as an IDENTITY column (there is no standard SQL for making this change).
Set the Identity Seed value [this is the same as the {StartValue} value that we have used
when discussing the SQL Server 2012 IDENTITY({StartValue}, {Increment}) property] to
the original value of 500, and SQL Server will set the next new value of WorkID to be the
maximum largest value of WorkID plus one. A different strategy is used for surrogate
keys with Oracle Database and MySQL, and these topics will be discussed in Chapters 10B
and 10B.
Now all that remains is to define the two triggers. This can be done by copying the text of
the old triggers and changing the name WORK to WORK_VERSION2.
At this point, tests should be run against the database to verify that all changes have
been made correctly. After that, stored procedures and applications that use WORK can be
changed to run against the new table name. 1 If all is correct, then the foreign key constraint
TransWorkFK and the WORK table can be dropped with the following:

/* *** EXAMPLE CODE - DO NOT RUN *** */
/* *** SQL-ALTER-TABLE-CH08-01 *** */
ALTER TABLE TRANS DROP CONSTRAINT TransWorkFK;
/* *** SQL-DROP-TABLE-CH08-01 *** */
DROP TABLE WORK;

The TransWorkFK constraint then can be added back to TRANS using the new name for
the work table:

/* *** EXAMPLE CODE - DO NOT RUN *** */
/* *** SQL-ALTER-TABLE-CH08-02 *** */
ALTER TABLE TRANS ADD CONSTRAINT TransWorkFK FOREIGN KEY(WorkID)

REFERENCES WORK_VERSION2(WorkID)

ON UPDATE NO ACTION

ON DELETE NO ACTION;

Clearly, there is more to changing a table name than you would think. You now can see
why some organizations do not allow programmers or users to employ the true name of a
table. Instead, views are described that serve as table aliases, as explained in Chapter 7. If this
were done here, only the views that define the aliases would need to be changed when the
source table name is changed as long as the view references all the columns in the table using

1 The timing is important. The WORK_VERSION2 table was created from WORK. If triggers, stored procedures,
and applications continue to run against WORK while the verification of WORK_VERSION2 is under way, then
WORK_VERSION2 will be out of date. Some action will need to be taken to bring it up to date before switching
the stored procedures and applications over to WORK_VERSION2.