Before using ALTER TABLE
Go Up to Altering Tables
Before modifying or dropping columns in a table, you need to do three things:
- 1. Make sure you have the proper database privileges.
- 2. Save the existing data.
- 3. Drop any constraints on the column.
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.
Before attempting to drop or modify a column, you should be aware of the different ways that ALTER
TABLE can fail:
- The person attempting to alter data does not have the required privileges.
- Current data in a table violates a
UNIQUEconstraint definition added to the table; there is duplicate data in columns that you are trying to define as
- The column to be dropped is part of a
- The column is used in a
CHECKconstraint. When altering a column based on a domain, you can supply an additional
CHECKconstraint for the column. Changes to tables that contain
CHECKconstraints with sub-queries can cause constraint violations.
- The column is used in another view, trigger, or in the value expression of a computed column.
- Important: You must drop the constraint or computed column before dropping the table column. You cannot drop
UNIQUEconstraints if they are referenced by
FOREIGN KEYconstraints. In this case, drop the
FOREIGN KEYconstraint before dropping the
UNIQUEkey it references. Finally, you can drop the column.
- Important: When you alter or drop a column, all data stored in it is lost.