Before using ALTER TABLE

From InterBase

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:

  1. Add a temporary column to the table whose definition mirrors the current column to be changed (the "old" column).
  2. Copy the data from the old column to the temporary column.
  3. Modify the old column.
  4. Copy the data from the temporary column to the old column.
  5. 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;
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.

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 or UNIQUE constraint definition added to the table; there is duplicate data in columns that you are trying to define as PRIMARY KEY or UNIQUE.
  • The column to be dropped is part of a UNIQUE, PRIMARY, or FOREIGN KEY constraint.
  • The column is used in a CHECK constraint. When altering a column based on a domain, you can supply an additional CHECK constraint for the column. Changes to tables that contain CHECK 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.
Important:
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.
Important:
When you alter or drop a column, all data stored in it is lost.

Advance To: