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]
[column_constraint] [SET LOCATION = (location_name)] {(,location_name)}
| 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
table_name
Specifies the name of the table to be altered.
ADD [COLUMN] column_name format [default_clause] [null_clause] [column_constraint] [SET LOCATION = (location_name) {(, location_name)}]
Adds a column. The column_name cannot already exist in the table.
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 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.
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 different locations, there are no "default locations," so you must specify location(s) for the new column.
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