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.
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.
Note: If the column is part of a foreign key in this table or referenced by a foreign key of another table, the data-type cannot be changed.
If a column default value is set for the column, it must be assignment-compatible with the values that already exist in the column.
Dropping a 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.
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.
You can add and drop table constraints using ALTER TABLE.
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.
If 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.
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.
column-definition, see CREATE TABLE.
table-constraint-definition, see CREATE TABLE.
default-value, see Default Values.
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.
See Limits for information on the maximum length of a row in a table.
EXTENDED Support for the ALTER option which allows the data type to be changed is a Mimer SQL extension.
Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40