Agile Data

The Rename Column Database Refactoring: A Complete Description

Follow @scottwambler on Twitter!

A database refactoring is a simple change to a database which improves its design without changing its semantics. In other words a database refactoring neither adds anything nor does it take anything away, it merely improves it. This article provides a complete description of the Rename Column database refactoring.  This description has been excerpted from the book Refactoring Databases: Evolutionary Database Design.  This description is typical of the 70 which appear in the book. The code presented is Java and Oracle PLSQL, but we could have chosen any major application programming language or RDBMS and implemented a similar solution.  In other words, this technique isn't specific to Oracle and Java.

Rename Column

Rename an existing table column.


Figure 1. Renaming the Customer.FName column.

 

Motivation

The primary reasons to apply Rename Column are to increase the readability of your database schema, to conform to accepted database naming conventions in your enterprise, or to enable database porting. For example, when you are porting from one database product to another, you may discover that the original column name cannot be used because it is a reserved key word in the new database.


Potential Trade-Offs

The primary trade-off is the cost of refactoring the external applications that access the column versus the improved readability and/or consistency provided by the new name.


Schema Update Mechanics

To rename a column, you must do the following:

  1. Introduce the new column. In Figure 1, we first add FirstName to the target table via the SQL command ADD COLUMN.
  2. Introduce a synchronization trigger. As you can see in Figure 1, you require a trigger to copy data from one column to the other during the transition period. This trigger must be invoked by any change to the data row.
  3. Rename other columns. If FName is used in other tables as (part of) a foreign key, you may want to apply Rename Column recursively to ensure naming consistency. For example, if Customer.CustomerNumber is renamed as Customer.CustomerID, you may want to go ahead and rename all instances of CustomerNumber in other tables. Therefore, Account.CustomerNumber will now be renamed to Account.CustomerID to keep the column names consistent.

The following code depicts the DDL to rename Customer.FName to Customer.FirstName and creates the SynchronizeFirstName trigger that synchronizes the data during the transition period. 

ALTER TABLE Customer ADD FirstName VARCHAR(40);

 

COMMENT ON Customer.FirstName ‘Renaming of FName column, finaldate = November 14 2007’;

COMMENT ON Customer.FName ‘Renamed to FirstName, dropdate = November 14 2007’;

UPDATE Customer SET FirstName = FName;

 

CREATE OR REPLACE TRIGGER SynchronizeFirstName

BEFORE INSERT OR UPDATE

ON Customer

REFERENCING OLD AS OLD NEW AS NEW

FOR EACH ROW

DECLARE

BEGIN

 IF INSERTING THEN

   IF :NEW.FirstName IS NULL THEN

     :NEW.FirstName := :NEW.FName;

   END IF;

   IF :NEW.Fname IS NULL THEN

     :NEW.FName := :NEW.FirstName;

   END IF;

 END IF;

 

 IF UPDATING THEN

   IF NOT(:NEW.FirstName=:OLD.FirstName) THEN

     :NEW.FName:=:NEW.FirstName;

   END IF;

   IF NOT(:NEW.FName=:OLD.FName) THEN

     :NEW.FirstName:=:NEW.FName;

   END IF;

 END IF;

 END;

The following DDL removes the original column and trigger after the transition period ends.

--After Nov 14 2007

DROP TRIGGER SynchronizeFirstName;

ALTER TABLE Customer DROP COLUMN FName;

Data Migration Mechanics

You need to copy all the data from the original column into the new column, in this case from FName to FirstName via the refactoring Move Data.


Access Program Update Mechanics

External programs that reference Customer.FName must be updated to reference columns by its new name. You should simply have to update any embedded SQL and/or mapping meta data. The following Hibernate mapping file depicts the "before mapping".

//Before mapping

<hibernate-mapping>

<class name="Customer" table="Customer">

 <id name="id" column="CUSTOMERID">

     <generator class="CustomerIdGenerator"/>

 </id>

 <property name="fName"/>

</class>

</hibernate-mapping>

The transition period mapping:

//Transition mapping

<hibernate-mapping>

<class name="Customer" table="Customer">

 <id name="id" column="CUSTOMERID">

     <generator class="CustomerIdGenerator"/>

 </id>

 <property name="firstName"/>

</class>

</hibernate-mapping>

The resulting mapping (which is the same as the transition mapping):

//After mapping

<hibernate-mapping>

<class name="Customer" table="Customer">

 <id name="id" column="CUSTOMERID">

     <generator class="CustomerIdGenerator"/>

 </id>

  <property name="firstName"/>

</class>

</hibernate-mapping>