Syntax
The ALTER TABLE statement has the following format:
[EXEC SQL] ALTER TABLE [schema.]table_name
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_table_name
| 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 table_name 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 table_name DROP [COLUMN] column_name RESTRICT | CASCADE
Drops a column. The column
column_name must exist in the table's
table_name. Only one column can be dropped in a single ALTER TABLE statement. One of the following options must be specified:
RESTRICT or CASCADE (see
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 table_name ADD CONSTRAINT constraint_name constraint_clause
ALTER TABLE table_name DROP CONSTRAINT constraint_name RESTRICT | CASCADE
ALTER TABLE table_name RENAME TO new_table_name
Renames a table. (For an alternative method, see
Rename Table (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 table_name 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 table_name ALTER [COLUMN] column_name
[SET GENERATED ALWAYS | SET GENERATED BY DEFAULT |
DROP IDENTITY]
datatype [default_clause] [null_clause]
[column_constraint] [COLLATE collation_name]
The ALTER TABLE table_name 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 one of the following:
UNICODE
Specifies collation for columns containing Unicode data (nchar and nvarchar data types). This is the default collation for Unicode columns.
UNICODE_CASE_INSENSITIVE
Specifies case insensitive collation for columns containing Unicode data (nchar and nvarchar data types).
UNICODE_FRENCH
Specifies French collation for columns containing Unicode data.
SQL_CHARACTER
Specifies the collation for columns containing char, C, varchar, and text data. This is the default collation for non-Unicode columns.