ALTER TABLE
Valid in: SQL, ESQL, OpenAPI, ODBC, JDBC, .NET
The ALTER TABLE statement can be used to:
• Add or remove a table-level constraint
• Add or remove a column from a base table
• Change characteristics of a column.
(Constraints can also be specified when the base table is created using the CREATE TABLE statement.)
Notes:
• To use this statement, the table must have a page size of 4K or larger.
• ALTER TABLE is not supported for tables that contain encrypted columns.
Caution! Take care when changing a base table column that is used in another database object (such as a view or database procedure). Certain actions—such as removing or adding a base table column, or changing the size of a base table column—will require all views that use the table to be dropped and recreated.
The ALTER TABLE statement has the following format:
[EXEC SQL] ALTER TABLE [schema.]tablename
ADD [COLUMN] column_name datatype [default_clause] [null_clause]
[MASKED [AS {BASIC | NULL | 0 | ' ' }] [column_constraint]
[SET add_column_set_options] [COLLATE collation_name]
| ALTER [COLUMN] column_name
[SET GENERATED ALWAYS | SET GENERATED BY DEFAULT | DROP IDENTITY]
datatype [default_clause] [null_clause]
[MASKED [AS {BASIC | NULL | 0 | ' ' }]
[column_constraint] [COLLATE collation_name]
| DROP [COLUMN] column_name RESTRICT | CASCADE
| ADD [CONSTRAINT constraint_name] constraint_spec
| DROP CONSTRAINT [IF EXISTS] constraint_name RESTRICT | CASCADE
| RENAME TO new_tablename
| RENAME [COLUMN] old_column_name TO new_column_name
X100 Only:
| ADD MINMAX {column_name [{, column_name}] | ALL} [SET MINMAX_SAMPLES]
| ALTER MINMAX ADD | DROP COLUMN column_name
| DROP MINMAX
ADD [COLUMN] column_name datatype [default_clause] [null_clause] [MASKED [AS {BASIC | NULL | 0 | ' ' }] [column_constraint]
[SET add_column_set_options] [COLLATE collation_name]
Adds a column. The column_name cannot exist in the table at the time the ALTER TABLE statement is issued.
The datatype, default_clause, null_clause, column_constraint, and collation_name of the column have the same structure as for the CREATE TABLE statement. However, for ANSI date data types (ansidate, time, and timestamp), you must specify a default value, which must be something other than CURRENT_DATE, CURRENT_TIME, LOCAL_TIME, CURRENT_TIMESTAMP or LOCAL_TIMESTAMP. For example:
Not permitted:
ALTER TABLE mytab ADD COLUMN newcol TIME WITH DEFAULT
ALTER TABLE mytab ADD COLUMN newcol TIME WITH DEFAULT CURRENT_TIME
Permitted:
ALTER TABLE mytab ADD COLUMN newcol TIME WITH DEFAULT '00:00:00'
The MASKED attribute will display the column with the specified mask characteristic unless the user has the UNMASK privilege. For more information on column masking, see the Security Guide.
WARNING! When altering a column to apply the MASKED attribute, existing optimizer statistics on the column may expose some data unmasked through the statdump command. If this is a concern, the existing column statistics should be dropped with statdump -zdl or the DROP STATISTICS statement.
The SET add_column_set_options are as follows:
LOCATION = (location_name) {(, location_name)}
SET LOCATION specifies the locations for the added column. The locations must already have been defined with the CREATE LOCATION statement. If multiple locations are specified, the column is horizontally partitioned across locations. If one location is specified, the column is stored in this location.
You must specify SET LOCATION unless all existing columns have the same location(s), in which case these are the default locations for the new column. If existing columns have separate locations, there are no “default locations,” so you must specify location(s) for the new column.
[NO]MINMAX
(X100 only) SET [NO]MINMAX specifies whether the column should participate in a min-max index. If specified as MINMAX, the table must already have an existing min-max index. The default is MINMAX if the table has a min-max index, or NOMINMAX if not. If a column has a min-max index, the column_has_minmax column in the iicolumns catalog will have a value of Y (yes).
The column is logically placed in the table definition after the last existing column. Only one column at a time can be added with the ALTER TABLE statement. When a column is added, the number of columns in the table cannot exceed the maximum number of columns in a table (which is 1024), and the row width cannot exceed the maximum row width for the page size.
Note: When a column is added to a table, the logical definition of the table is changed without physically reorganizing the data. Therefore, after adding columns, use the modify command to rebuild the table.
ALTER [COLUMN] column_name
[SET GENERATED ALWAYS | SET GENERATED BY DEFAULT |
DROP IDENTITY]
datatype [default_clause] [null_clause]
[MASKED [AS {BASIC | NULL | 0 | ' ' }]
[column_constraint] [COLLATE collation_name]
The ALTER TABLE tablename ALTER COLUMN column_name statement changes the characteristics of a column.
Note: Not all ALTER COLUMN operations are supported for X100 tables.
Note: A column cannot be altered or dropped if it is a key column.
The ALTER TABLE...ALTER COLUMN statement can be used to:
• Switch between identity column modes (SET GENERATED ALWAYS or SET GENERATED BY DEFAULT) or remove the identity attribute of a column (DROP IDENTITY). For details, see
CREATE TABLE.
Note: The ALWAYS or BY DEFAULT options can be used only on identity columns.
• Change the size of a character column to preserve the existing default_clause, null_clause, column_constraint, and collation_name.
• Change the column from a non-Unicode data type to a Unicode data type.
• Change from one character data type to another.
• Change a column from not null to null.
• Change the default value of a column.
Note: The default value of a column cannot be changed to null.
DROP [COLUMN] column_name RESTRICT | CASCADE
Drops a column. The column
column_name must exist in the table's
tablename. Only one column can be dropped in a single ALTER TABLE statement. Either
RESTRICT or CASCADE must be specified (see
Restrict and Cascade).
Note: A column cannot be dropped that is being used as a partitioning column or storage structure key column.
Note: When a column is dropped, the logical definition of the table is changed without physically reorganizing the data. The column number and the space associated with the dropped column are not reused. After dropping columns, use the modify command to clean up space and to rebuild the table.
ADD CONSTRAINT constraint_name constraint_clause
DROP CONSTRAINT [IF EXISTS] constraint_name RESTRICT | CASCADE
IF EXISTS suppresses error reporting for the specified constraint if it does not exist and the user matches the schema.
RENAME TO new_tablename
Renames a table. (For an alternative method, see
RENAME TABLE.)
Indexes, grants, comments, synonyms, sequences, partitions, and extension tables are automatically transferred to the newly renamed table or column. For grants, query text in the iiqrytext catalog is updated to reflect the new table and column names.
The rename operation will fail with an error if any views, procedures, referential or check constraints, rules, integrities, non-grant permits, or security alarms depend on the old table name.
Any forms, join definitions, or reports that refer to the old table name will be invalidated; these must be recreated and reloaded. Any copydb (unloaddb) scripts generated before renaming the table can no longer be used for reload operations.
RENAME [COLUMN] old_column_name TO new_column_name
Renames a table column.
The rename operation will fail with an error if any views, constraints or integrities depend on the column being renamed. The rename operation also fails if any procedures, rules, referential or check constraints, non-grant permits, or security alarms depend on the table of the column being renamed.
Any forms, join definitions, or reports that refer to the old column name will be invalidated; these must be recreated and reloaded. Any copydb (unloaddb) scripts generated before renaming the column can no longer be ued for reload operations.
ADD MINMAX {column_name [{, column_name}] |ALL} [SET [NO]MINMAX_SAMPLES]
X100 Only. Adds a min-max index on the specified columns. If the column list is specified as ALL, all columns are included in the new min-max index.
The value Y (yes) or N (no) for the column_has_minmax column in the iicolumns system catalog indicates whether a column has a min-max index.
Optionally, specifies whether the min-max index should be sampled. The default is NOMINMAX_SAMPLES. The MINMAX_SAMPLES setting becomes the new permanent minmax-samples property of the table. For more information, see
MINMAX_SAMPLES Option.
Note: You cannot alter an existing min-max index to be sampled or not-sampled; you must drop and re-add it.
ALTER MINMAX ADD | DROP COLUMN column_name
X100 Only. Adds or drops the specified column from the min-max index.
DROP MINMAX
X100 Only. Drops the min-max index from the table.
Embedded Usage
In an embedded ALTER TABLE statement, specify the WITH clause using a host string variable (WITH :hostvar).
Permissions
To add or drop constraints or columns for a table, you must own the table.
To define a referential constraint that refers to a table owned by another user, you must have the REFERENCES privilege for the columns to which the constraint refers.
Locking
The ALTER TABLE statement acquires an exclusive lock on the table at the start of execution. The lock is held until the end of the transaction.
Related Statements
CREATE INDEX
CREATE TABLE
MODIFY