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:
- Introduce the new column. In Figure 1, we first add FirstName to the target table via the SQL command ADD COLUMN.
- 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.
- 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> |