ALTER TABLE
Valid in: SQL, ESQL, OpenAPI, ODBC, JDBC, .NET
The ALTER TABLE statement can be used to:
• Add or remove a column.
• Add or remove a table-level constraint.
• Rename tables and columns. (For an alternative method of renaming tables, see
RENAME TABLE.)
Note: Adding or dropping a column is allowed only if there are no in-memory DML changes against the table. Use the MODIFY...TO COMBINE command to write the in-memory changes to disk.
This statement has the following format:
ALTER TABLE [schema.]table_name
ADD [COLUMN] column_name format [default_clause] [null_clause]
[MASKED [AS {BASIC | NULL | 0 | ' ' }] [column_constraint]
[SET add_column_set_options]
| ALTER [COLUMN] column_name format [default_clause] [null_clause]
[MASKED [AS {BASIC | NULL | 0 | ' ' }] [column_constraint]
| DROP [COLUMN] column_name RESTRICT
| ADD [CONSTRAINT constraint_name] constraint_spec
| DROP CONSTRAINT constraint_name RESTRICT
| RENAME TO new_table_name
| RENAME [COLUMN] old_column_name TO new_column_name
| ADD MINMAX {column_name [{, column_name}] | ALL} [SET MINMAX_SAMPLES]
| ALTER MINMAX ADD | DROP COLUMN column_name
| DROP MINMAX
table_name
Specifies the name of the table to be altered.
ADD [COLUMN] column_name format [default_clause] [null_clause] [MASKED [AS {BASIC | NULL | 0 | ' ' }] [column_constraint] [SET add_column_set_options]
Adds a column. The column_name cannot already exist in the table.
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. The number of columns in the table and the row width cannot exceed the limits.
The format, default_clause, null_clause, and column_constraint have the same structure as for the CREATE TABLE statement, except that NOT NULL NOT DEFAULT is not allowed.
The SET add_column_set_options are:
[NO]MINMAX
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.
Note: If a column has a min-max index, the column_has_minmax column in the iicolumns catalog will have a value of Y (yes).
ALTER [COLUMN] column_name format [default_clause] [null_clause] [MASKED [AS {BASIC | NULL | 0 | ' ' }] [column_constraint]
DROP [COLUMN] column_name RESTRICT
Drops a column. The column_name must exist in the specified table. Only one column can be dropped in a single ALTER TABLE statement.
ADD [CONSTRAINT constraint_name] constraint_spec
DROP CONSTRAINT constraint_name RESTRICT
Drops a table-level constraint.
Notes:
• Unique and primary keys that are referenced by foreign keys cannot be dropped; the foreign key must be dropped first.
• Foreign keys with indexes defined on them cannot be dropped; the index must be dropped first.
RESTRICT Option
The RESTRICT option does not drop the column or constraint if one or more objects exist that depend on it. For example:
• A view with reference to the column in the base table
• An index defined with this column
RENAME TO new_table_name
RENAME [COLUMN] old_column_name TO new_column_name
ADD MINMAX {column_name [{, column_name}] |ALL} [SET [NO]MINMAX_SAMPLES]
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.
Note: The column_has_minmax column in the iicolumns system catalog indicates whether a column has a min-max index by the value Y (yes) or N (no).
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
Adds or drops the specified column from the min-max index.
DROP MINMAX
Drops the min-max index from the table.