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
| DROP MINMAX
| ADD MINMAX {column_name [{, column_name}] | ALL} [SET MINMAX_SAMPLES]
| ALTER MINMAX ADD COLUMN column_name [SET NOAUTO_TUNE]
| ALTER MINMAX DROP COLUMN column_name
| ALTER MINMAX SET AUTO_TUNE
| ALTER MINMAX SET AUTO_TUNE IGNORING column_name {, column_name}
| ALTER MINMAX SET NOAUTO_TUNE
ALTER TABLE [schema.]table_name {,[schema.]table_name}| ALL [username] TABLES
| ALTER MINMAX SET AUTO_TUNE
| ALTER MINMAX SET NOAUTO_TUNE
Specifies the name of the table to be altered.
If ALTER MINMAX SET [NO]AUTO_TUNE is used, multiple tables can be specified. You can use ALL TABLES to specify all tables in the database. An optional [username] can be used with ALL TABLES to alter all tables belonging to a specific username.
If ALTER MINMAX SET [NO]AUTO_TUNE is not used, only one table can be specified.
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 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
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).
NOAUTO_TUNE
SET NOAUTO_TUNE indicates that this column should not be considered for inclusion into the smart minmax index if smart minmax has been enabled for the table.
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
DROP MINMAX
Drops the min-max index from the table.
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 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
Adds or drops the specified column from the min-max index.
ALTER MINMAX SET AUTO_TUNE
Enables smart minmax for the specified tables. All columns will be considered for inclusion into the smart minmax index.
ALTER MINMAX SET AUTO_TUNE IGNORING column_name {, column_name }
Enables smart minmax for the specified table. If the ignoring clause exists, then the columns listed are not considered for inclusion into the smart minmax index. If the ignoring clause does not exist, all columns are considered for inclusion.
ALTER MINMAX SET NOAUTO_TUNE
Disables smart minmax for the specified tables.