Modifying Columns in a Table

Go Up to Using ALTER 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 datatype_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:	Blob	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