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.
An example:
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 OFFICE_NO
to TEMP_NO
to preserve it:
UPDATE EMPLOYEE
SET TEMP_NO = OFFICE_NO;
Modify OFFICE_NO
, specifying the data type and new size:
ALTER TABLE ALTER OFFICE_NO TYPE CHAR(4);
Move the data from TEMP_NO
to OFFICE_NO
:
UPDATE EMPLOYEE
SET OFFICE_NO = TEMP_NO;
Finally, drop the TEMP_NO
column:
ALTER TABLE DROP TEMP_NO;
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.Dropping Columns
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
PRIMARY KEY
orUNIQUE
constraint definition added to the table; there is duplicate data in columns that you are trying to define asPRIMARY KEY
orUNIQUE
. - The column to be dropped is part of a
UNIQUE
,PRIMARY
, orFOREIGN KEY
constraint. - The column is used in a
CHECK
constraint. When altering a column based on a domain, you can supply an additionalCHECK
constraint for the column. Changes to tables that containCHECK
constraints with sub-queries can cause constraint violations. - The column is used in another view, trigger, or in the value expression of a computed column.
You must drop the constraint or computed column before dropping the table column. You cannot drop
PRIMARY KEY
and UNIQUE
constraints if they are referenced by FOREIGN KEY
constraints. In this case, drop the FOREIGN KEY
constraint before dropping the PRIMARY KEY
or UNIQUE
key it references. Finally, you can drop the column.When you alter or drop a column, all data stored in it is lost.