ALTER TABLE

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

Usage

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.

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.

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. This is normally only made when using the Mimer SQL Real-Time edition. See the Mimer SQL Real-Time Manual for more information.

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 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 that already exists on the table, 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

Restrictions

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

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

Notes

Standard Compliance


Standard	Compliance	Comments
SQL-2003	Core	Fully compliant.
SQL-2003	Features outside core	Feature F033, "ALTER TABLE statement: DROP COLUMN clause" Feature F381, "Extended schema manipulation" support for set column default and drop column default and support for add and drop table constraints.
	Mimer SQL extension	Support for changing the data type for a column is a Mimer SQL extension. The WITH/WITHOUT CHECK clause is a Mimer SQL extension. The SET COMPRESS and SET PAGESIZE clauses are Mimer SQL extensions.