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.
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
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
Altering Table's Disk Representation
SET PAGESIZEclauses 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
RESTRICTspecify the action to be taken if other objects (such as views, table constraints, indexes, routines and triggers) exist which reference the column being dropped.
CASCADEis specified, referencing objects will be dropped as well.
RESTRICTis 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,
RESTRICTbehavior 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.
WITHOUT CHECKclauses are used to control whether existing table data should be verified against the constraint or not.
WITH CHECKis the default behavior.
WITH CHECKis used and the existing data in a table violates the table constraint being added, the
ALTER TABLEstatement 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
RESTRICTspecify the action to be taken in the case of a referential constraint being dropped.
CASCADEis specified when a referential constraint is dropped, any other referential constraints which are referencing the unique key being dropped will also be dropped.
RESTRICTis 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,
RESTRICTbehavior 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 TABLEoperation must have
USAGEprivilege on any domain, user-defined type or sequence involved,
EXECUTEprivilege on any function or method involved and
REFERENCESprivilege 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.
NCLOB) data type is not allowed in any type of table constraint.
ExamplesALTER TABLE staff ADD city VARCHAR(50); ALTER TABLE staff ALTER COLUMN city SET DATA TYPE NCHAR VARYING(50)
See Limits for information on the maximum length of a row in a table.
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 Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40