Using ALTER TABLE

Add new column definitions. To create a column using an existing name, you must drop existing column definitions before adding new ones.
Add new table constraints. To create a constraint using an existing name, you must drop existing constraints with that name before adding a new one.
Drop existing column definitions without adding new ones.
Drop existing table constraints without adding new ones.
Modify column names, data types, and position

For a detailed specification of ALTER TABLE syntax, see the Language Reference.

Adding a New Column to a Table

The syntax for adding a column with ALTER TABLE is:

ALTER TABLE table ADD <col_def>
<col_def> = col {<data_type>
| [COMPUTED [BY] (<expr>)
| domain}
 [DEFAULT {literal | NULL | USER}]
 [NOT NULL] [<col_constraint>]

 [COLLATE collation]
<col_constraint> = [CONSTRAINT constraint] <constraint_def>
 [<col_constraint>]
<constraint_def> =
PRIMARY KEY
| UNIQUE
 | CHECK (<search_condition>)
 | REFERENCES other_table [(other_col [, other_col …])]
[ON DELETE {RESTRICT|NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
[ON UPDATE {RESTRICT|NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

For the complete syntax of ALTER TABLE, see the Language Reference.

For example, the following statement adds a column, EMP_NO, to the EMPLOYEE table using the EMPNO domain:

ALTER TABLE EMPLOYEE ADD EMP_NO EMPNO NOT NULL;

You can add multiple columns to a table at the same time. Separate column definitions with commas. For example, the following statement adds two columns, EMP_NO, and FULL_NAME, to the EMPLOYEE table. FULL_NAME is a computed column, a column that derives it values from calculations based on two other columns already defined for the EMPLOYEE table:

ALTER TABLE EMPLOYEE
 ADD EMP_NO EMPNO NOT NULL,
 ADD FULL_NAME COMPUTED BY (LAST_NAME || ', ' || FIRST_NAME);

You can also define integrity constraints for columns that you add to the table. For example, the next statement adds two columns, CAPITAL and LARGEST_CITY, to the COUNTRY table, and defines a UNIQUE constraint on CAPITAL:

ALTER TABLE COUNTRY
 ADD CAPITAL VARCHAR(25) UNIQUE,
 ADD LARGEST_CITY VARCHAR(25) NOT NULL;

Adding New Table Constraints

You can use ALTER TABLE to add a new table-level constraint. The syntax is:

ALTER TABLE name ADD [CONSTRAINT constraint] <tconstraint_opt>;

where <tconstraint_opt> is a PRIMARY KEY, FOREIGN KEY, UNIQUE, or CHECK constraint. For example:

ALTER TABLE EMPLOYEE
 ADD CONSTRAINT DEPT_NO UNIQUE(PHONE_EXT);

Dropping an Existing Column from a Table

You can use ALTER TABLE to delete a column definition and its data from a table. A column can be dropped only by the owner of the table. If another user is accessing a table when you attempt to drop a column, the other user’s transaction will continue to have access to the table until that transaction completes. InterBase postpones the drop until the table is no longer in use.

The syntax for dropping a column with ALTER TABLE is:

ALTER TABLE name DROP colname [, colname ...];

For example, the following statement drops the EMP_NO column from the EMPLOYEE table:

ALTER TABLE EMPLOYEE DROP EMP_NO;

Multiple columns can be dropped with a single ALTER TABLE statement.

ALTER TABLE EMPLOYEE
 DROP EMP_NO,
 DROP FULL_NAME;

Important:
You cannot delete a column that is part of a UNIQUE, PRIMARY KEY, or FOREIGN KEY constraint. In the previous example, EMP_NO is the PRIMARY KEY for the EMPLOYEE table, so you cannot drop this column unless you first drop the PRIMARY KEY constraint.

Dropping Existing Constraints from a Column

You must drop constraints from a column in the correct sequence. See the following CREATE TABLE example. Because there is a foreign key in the PROJECT table that references the primary key (EMP_NO) of the EMPLOYEE table, you must first drop the foreign key reference before you can drop the PRIMARY KEY constraint in the EMPLOYEE table.

CREATE TABLE PROJECT
(PROJ_ID PROJNO NOT NULL,
 PROJ_NAME VARCHAR(20) NOT NULL UNIQUE,
 PROJ_DESC BLOB(800,1),
 TEAM_LEADER EMPNO,
 PRODUCT PRODTYPE,
 PRIMARY KEY (PROJ_ID),
 CONSTRAINT TEAM_CONSTRT FOREIGN KEY (TEAM_LEADER) REFERENCES
 EMPLOYEE (EMP_NO));

The proper sequence is:

ALTER TABLE PROJECT
 DROP CONSTRAINT TEAM_CONSTRT;
ALTER TABLE EMPLOYEE
 DROP CONSTRAINT EMP_NO_CONSTRT;
ALTER TABLE EMPLOYEE
 DROP EMP_NO;

Note:
Constraint names are in the system table, RDB$RELATION_CONSTRAINTS.

In addition, you cannot delete a column if it is referenced by another column’s CHECK constraint. To drop the column, first drop the CHECK constraint, then drop the column.

Modifying Columns in a Table

The syntax for modifying a column with ALTER TABLE is:

ALTER TABLE table ALTER [COLUMN]simple_column_name alter_rel_field
alter_rel_field = new_col_name | new_col_type | new_col_pos
new_col_name = TO simple_column_name
new_col_type = TYPE data_type_or_domain
new_col_pos = POSITION integer

For the complete syntax of ALTER TABLE, see Language Reference Guide.

For example, the following statement moves a column, EMP_NO, from the third position to the second position in the EMPLOYEE table:

ALTER TABLE EMPLOYEE ALTER EMP_NO POSITION 2;

You could also change the name of the EMP_NO column to EMP_NUM as in the following example:

ALTER TABLE EMPLOYEE ALTER EMP_NO TO EMP_NUM;

The next example shows how to change the data type of the EMP_NUM column to CHAR(20):

ALTER TABLE EMPLOYEE ALTER EMP_NUM TYPE CHAR(20);

Conversions from non-character to character data are allowed with the following restrictions:

Blob and array types are not convertible.
Field types (character or numeric) cannot be shortened.
The new field definition must be able to hold the existing data (for example, the new field has too few CHAR values or the data type conversion is not supported) or an error is returned.

Note:
Conversions from character data to non-character data are not allowed.

Important:
Any changes to the field definitions may require the indexes to be rebuilt.

The table below graphs all valid conversions; if the conversion is valid (converting from the item on the side column to the item in the top row) it is marked with an X.

Valid data type conversions using ALTER COLUMN and ALTER DOMAIN
Convert:	Boolean	Char	Date	Dec.	Dble	Flo	Int.	Num.	Tstmp	Time	Smlint	Var.
Blob
Boolean	X	X										X
Char	X	X										X
Date		X	X						X
Decimal		X		X				X				X
Double		X			X	X						X
Float		X			X	X						X
Integer		X		X	X		X	X				X
Numeric		X						X				X
Timestamp		X							X	X
Time		X							X	X
Smallint		X		X	X	X	X	X			X	X
Varchar	X	X										X

Summary of ALTER TABLE Arguments

When you use ALTER TABLE to add column definitions and constraints, you can specify all of the same arguments that you use in CREATE TABLE; all column definitions, constraints, and data type arguments are the same, with the exception of the <operation> argument. The following operations are available for ALTER TABLE.

Add a new column definition with ADD <col_def>.

Add a new table constraint with ADD <table_constraint>.

Drop an existing column with DROP <col>.

Drop an existing constraint with DROP CONSTRAINT <constraint>.
Modify column names, data types, and positions

Advance To:

Dropping Tables