ALTER TABLE

Alters a table definition by: adding a column or table constraint; dropping a column or a table constraint; changing the data-type or the default value for a column; setting disk representation.

 

where set-data-type is:

 

Usage

Embedded, Interactive, Module, ODBC, JDBC.

Description

Adding a Column

When a column is added, the existing table is extended with the addition of a new column, which is placed at the end of the table definition.

For each existing row in the table, the column will be assigned the default value (which will be the column default value if one is defined, the domain default if the column belongs to a domain or otherwise the null value).

Note:If the column-definition of the column being added includes the NOT NULL column constraint, then the column must either have a non-null default value defined or belong to a domain with a non-null default value. Otherwise an attempt would be made to insert the null value into a column which cannot accept it.

For information on column-definition, please see CREATE TABLE.

Altering a Column

When a column is altered, it is possible to change the data type of the data in it and to set or drop the column default value.

If a new data type is set for the column, it must be assignment-compatible with the values that already exist in the column.

If a column default value is set for the column, it must be assignment-compatible with the values that already exist in the column.

When the column default value is dropped, the column takes its default value from the domain to which the column belongs (if it uses a domain), otherwise the column default becomes the null value.

Altering Table’s Disk Representation

The SET COMPRESS and SET PAGESIZE clauses make it possible to override a decision the server has taken regarding how data is represented on disk.

Valid page-sizes are 4, 32 and 128 K.

Dropping a Column

When a column is dropped, it is removed from the table. The keywords CASCADE and RESTRICT specify the action to be taken if other objects (such as views, table constraints, indexes, routines and triggers) exist which reference the column being dropped.

If CASCADE is specified, referencing objects will be dropped as well.

If RESTRICT is specified, an error will be raised if referencing objects exist and neither the column nor the referencing objects will be dropped. If neither keyword is specified, RESTRICT behavior is the default.

Adding a Table Constraint

It is possible to add a new table constraint to the table, which is specified in the same way it would be when a new table is created. If the table constraint is explicitly named, it cannot have the same name as a constraint, table, view, synonym or index that already exists in the schema in which the table is created. See CREATE TABLE for more details.

The WITH CHECK and WITHOUT CHECK clauses are used to control whether existing table data should be verified against the constraint or not. WITH CHECK is the default behavior.

If WITH CHECK is used and the existing data in a table violates the table constraint being added, the ALTER TABLE statement will fail and the new constraint will not be added to the table.

Note:For a table in a database with the AUTOUPGRADE attribute enabled, the WITHOUT CHECK option must be used when adding constraints. Please note that changing the primary key composition may lead to loss of data if the modification results in primary key duplicates among existing data (duplicates will silently be removed).

See ALTER DATABASE for more information about AUTOUPGRADE.

Dropping Table Constraints

It is also possible to drop an existing table constraint in order to remove the constraint from the table. The keywords CASCADE and RESTRICT specify the action to be taken in the case of a referential constraint being dropped.

If CASCADE is specified when a referential constraint is dropped, any other referential constraints which are referencing the unique key being dropped will also be dropped.

If RESTRICT is specified an error will be raised, and nothing will be dropped, if there are other referential constraints referencing the one to be dropped. If neither keyword is specified, RESTRICT behavior is the default.

Language Elements

column-definition, see CREATE TABLE.

table-constraint-definition, see CREATE TABLE.

default-value, see Default Values.

Restrictions

A table can only be altered by the creator of the schema to which the table belongs.

You must have exclusive access to a table to alter it.

A column cannot be dropped if it is the only column in a table (i.e. a drop column operation may not result in a table with no columns).

The ident performing an ALTER TABLE operation must have USAGE privilege on any domain or sequence involved, EXECUTE privilege on any function involved and REFERENCES privilege on all columns specified in references of a referential constraint.

Change of data type for a column is not allowed if the column participates in any type of table constraint (PRIMARY KEY, UNIQUE, REFERENTIAL) or index (INDEX, UNIQUE INDEX).

Change of data type for a column is not allowed if the column is used by a view, procedure, function or trigger.

If a UNIQUE constraint is added to the table, it must be stored in a databank with the TRANSACTION or LOG option.

If a REFERENTIAL constraint is added to the table, both the referencing table and the referenced table must be stored in a databank with the TRANSACTION or LOG option.

If any record exists for a table it is not allowed to add a column with PRIMARY KEY or UNIQUE constraint.

A column of LARGE OBJECT (i.e. BLOB, CLOB, or NCLOB) data type is not allowed in any type of table constraint.

Examples

ALTER TABLE staff ADD city VARCHAR(50);

 

ALTER TABLE staff ALTER COLUMN city SET DATA TYPE NCHAR VARYING(50)
    COLLATE english_1;

Notes

See Limits for information on the maximum length of a row in a table.

Standard Compliance