Saving Existing Data
Before modifying an existing column definition using
ALTER TABLE, you must preserve existing data, or it will be lost.
Preserving data in a column and modifying the definition for a column is a five-step process:
- Add a temporary column to the table whose definition mirrors the current column to be changed (the "old" column).
- Copy the data from the old column to the temporary column.
- Modify the old column.
- Copy the data from the temporary column to the old column.
- Drop the temporary column.
For example, suppose the
EMPLOYEE table contains a column,
OFFICE_NO, defined to hold a data type of
CHAR (3), and suppose that the size of the column needs to be increased by one.
The following example describes each step and provides sample code:
First, create a temporary column to hold the data in
OFFICE_NO during the modification process:
ALTER TABLE EMPLOYEE ADD TEMP_NO CHAR(3);
Move existing data from
TEMP_NO to preserve it:
UPDATE EMPLOYEE SET TEMP_NO = OFFICE_NO;
OFFICE_NO, specifying the data type and new size:
ALTER TABLE ALTER OFFICE_NO TYPE CHAR(4);
Move the data from
UPDATE EMPLOYEE SET OFFICE_NO = TEMP_NO;
Finally, drop the
ALTER TABLE DROP TEMP_NO;
- Note: This is the safest, most conservative method for altering a column, following the rule that you should always save existing data before modifying metadata. But for experienced InterBase users, there is a faster, one-step process. You can alter the column without first copying the data, for example:
ALTER TABLE EMPLOYEE ALTER COLUMN OFFICE_NO TYPE CHAR(4)which achieves the same end as the five-step process example.