Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


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

Valid page-sizes are 2, 16, and 64 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 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

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, user-defined type or sequence involved, EXECUTE privilege on any function or method 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, method 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

Standard
Compliance
Comments
SQL-2011
Core
Fully compliant.
SQL-2011
Features outside core
Feature F033, "ALTER TABLE statement: DROP COLUMN clause"
Feature F381, "Extended schema manipulation". Support for drop schema statement, alter column definition, set column default clause, drop column default clause, add table constraint definition, and drop table constraint definition.
Feature F382, "Alter column data type".

Mimer SQL extension
The WITH/WITHOUT CHECK clause is a Mimer SQL extension.
The SET COMPRESS and SET PAGESIZE clauses are Mimer SQL extensions.


Mimer
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se
Mimer SQL Documentation TOC PREV NEXT INDEX