Alter Table

Valid in: SQL, ESQL

The ALTER TABLE statement adds or removes a table-level constraint or a column from a base table.

The statement also modifies the character columns in a table to Unicode column types.

Constraints can also be specified when the base table is created using the CREATE TABLE statement.

Note: To use this statement, the table must have a page size of 4K or larger.

Syntax

The Alter Table statement has the following format:

[EXEC SQL] ALTER TABLE [schema.]table_name

ADD [column] column_name FORMAT [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

| ALTER [COLUMN] column_name FORMAT [default_clause]

        [null_clause] [column_constraint] [COLLATE collation_name]

• ALTER TABLE table_name ADD [COLUMN] column_name FORMAT [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 format default_clause, null_clause, column_constraint, and collation_name of the column have the same structure as for the CREATE TABLE statement, except that WITH NULL WITH DEFAULT and NOT NULL NOT DEFAULT are 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. 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 or the max_tuple_length setting.

  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.

  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

  Adds a table-level constraint.

• ALTER TABLE table_name DROP CONSTRAINT constraint_name RESTRICT | CASCADE

  Drops a constraint.

  One of the following options must be specified: restrict or cascade.

• ALTER TABLE table_name ALTER [COLUMN] column_name FORMAT [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.

  This statement can be used to:

  • 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.

    Note: The database must be Unicode enabled either having been created as a Unicode-enabled database with the –i (Normalization Form C (NFC)) or –n (Normalization Form D (NFD)) flag, or by using the alterdb command.

  • 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).
    • SQL_CHARACTER—Specifies the collation for columns containing char, C, varchar, and text data. This is the default collation for non-Unicode columns.