SQL Reference Guide > SQL Reference Guide > SQL Statements > ALTER TABLE
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 table-level constraint
• Add or remove a column from a base table
• Rename tables and columns. (For an alternative method of renaming tables, see RENAME TABLE.) For information about rules and restrictions, see Rules and Restrictions on Renaming Tables and Rules and Restrictions on Renaming Columns.
• 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] [column_constraint] [COLLATE collation_name]
| DROP [COLUMN] column_name RESTRICT | CASCADE
| ADD [CONSTRAINT constraint_name] constraint_spec
| DROP CONSTRAINT constraint_name RESTRICT | CASCADE
| RENAME TO new_tablename
| RENAME [COLUMN] old_column_name TO new_column_name
| ALTER [COLUMN] column_name
        [SET GENERATED ALWAYS | SET GENERATED BY DEFAULT | DROP IDENTITY]
        datatype [default_clause] [null_clause]
        [column_constraint] [COLLATE collation_name]
ALTER TABLE tablename ADD [COLUMN] column_name datatype [default_clause] [null_clause] [column_constraint] [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 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 TABLE tablename 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. One of the following options must be specified: RESTRICT or CASCADE (see page 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.
ALTER TABLE tablename ADD CONSTRAINT constraint_name constraint_clause
Adds a table-level constraint (see page Constraint Specifications).
ALTER TABLE tablename DROP CONSTRAINT constraint_name RESTRICT | CASCADE
Drops a constraint (see page Constraint Specifications).
One of the following options must be specified: RESTRICT or CASCADE (see page Restrict and Cascade).
ALTER TABLE tablename 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.
ALTER TABLE tablename 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.
ALTER TABLE tablename ALTER [COLUMN] column_name
[SET GENERATED ALWAYS | SET GENERATED BY DEFAULT |
  DROP IDENTITY]
datatype [default_clause] [null_clause]
[column_constraint] [COLLATE collation_name]
Note:  Not supported for X100 tables.
The ALTER TABLE tablename ALTER COLUMN column_name statement changes the characteristics of a column.
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.
• Change the collation sequence of the column. The collation_name can be UCS_BASIC, UNICODE, UNICODE_CASE_INSENSITIVE, UNICODE_FRENCH, or SQL_CHARACTER. For details, see Column Specification (on page Column Specification--Define Column Characteristics).
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
Last modified date: 01/30/2023