Was this helpful?
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]
Changes the characteristics of a column. For more information, see ALTER TABLE...ALTER COLUMN Restrictions.
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
Adds a table-level constraint (see Constraint Specifications).
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
Renames a table column. See Rules and Restrictions on Renaming Columns.
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.
Last modified date: 10/30/2024