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 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
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 4, 32 and 128 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, table, view, synonym or index that already exists in the schema in which the table is created. 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
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 or sequence involved,
EXECUTEprivilege on any function 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 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-2016 Core Fully compliant. SQL-2016 Features outside core Feature F033, "ALTER TABLE statement: DROP COLUMN clause"Feature F191, "Referential delete actions".Feature F251, "Domain support".Feature F381, "Extended schema manipulation".Feature F382, "Alter column data type".Feature F491, "Constraint management", support for named constraints.Feature T591, "UNIQUE constraints of possibly null columns".Feature F690, "Collation support".Feature F701, "Referential update actions".Feature F721, "Deferrable constraints", only for referential constraints. 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