Show TOC

Background documentationMODIFY Definition (modify_definition) Locate this document in the navigation structure

 

You can modify data types and column attributes by specifying a MODIFY definition (modify_definition) in the ALTER TABLE statement.

Structure

Syntax Syntax

  1. <modify_defnition> ::=
  MODIFY (<modify_column_definition>,...)

<modify_column_definition> ::=
  <column_name> <data_type> <column_attributes>
| <column_name> <data_type>
| <column_name> <column_attributes>
End of the code.
Examples

SQL Tutorial, Tables

Explanation

The parentheses are not necessary if the MODIFY definition only contains one column_name.

Each column name must be a column of the base table specified in the ALTER TABLE statement.

If a column identified by column_name  is contained in a CONSTRAINT definition for the table, this column must also define a valid search condition once the data type has been modified.

<data_type>

If a DEFAULT specification is not specified and if a DEFAULT specification is defined for the corresponding column, it must be compatible with the data_type.

  • Code attribute ASCII: The corresponding column must possess the data type DATE, TIME or TIMESTAMP or the code attribute ASCII or UNICODE before modification.

  • Code attribute UNICODE: A transformation from UNICODE to ASCII must be possible for the corresponding column.

  • Code attribute BYTE: The corresponding column must possess the data type DATE, TIME or TIMESTAMP or the code attribute ASCII or BYTE before modification.

Data type CHAR(n), VARCHAR(n): The corresponding column must possess the data type CHAR(n), VARCHAR(n), DATE, TIME or TIMESTAMP. In this case, the table must not contain a row in which the column has a value with a length greater than n. If a column had the code attribute UNICODE before the ALTER TABLE statement was executed, and the new code attribute is not UNICODE, transformation to the new code attribute must be possible.

Data type DATE: The corresponding column must possess the data type CHAR(n), VARCHAR(n) or DATE. This column must contain a date value in any of the date formats supported by the database system in all rows of the table.

Data type FIXED (n,m): The corresponding column must possess the data type FIXED(n,m), FLOAT, INT or SMALLINT. In this case, the table must not contain a row that has a value with more than (n - m) integral or m fractional digits in the column.

Data type FLOAT(n): The corresponding column must possess the data type FIXED(n,m), FLOAT(n), INT or SMALLINT.

Data type INT: The corresponding column must possess the data type FIXED(n,m), FLOAT(n), INT or SMALLINT. In this case, the table must only contain rows containing integral values between -2147483648 and 2147483647 in the column.

Data type SMALLINT: The corresponding column must possess the data type FIXED(n,m), FLOAT(n), INT or SMALLINT. In this case, the table must only contain rows containing integral values between -32768 and 32767 in the column.

Data type TIME: The corresponding column must possess the data type CHAR(n), VARCHAR(n) or TIME. This column must contain a time value in any of the time formats supported by the database system in all rows of the table.

Data type TIMESTAMP: The corresponding column must possess the data type CHAR(n), VARCHAR(n) or TIMESTAMP. This column must contain a timestamp value in any of the time stamp formats supported by the database system in all rows of the table.

Column attribute NULL: A NULL value can be entered in the corresponding column with a subsequent INSERT or UPDATE statement.

<column_attributes>

The following column attributes only are allowed:

  • NULL

    NULL Value

  • NOT NULL

    When NOT NULL is specified, no rows in the table may contain a NULL value in the corresponding column. A NULL value can no longer be inserted into the column after being modified.

  • DEFAULT Specification

    The DEFAULT specification DEFAULT SERIAL is not permitted. If a DEFAULT specification is specified, it replaces an existing DEFAULT specification in the corresponding column. The new DEFAULT specification only affects subsequent INSERT statements and does not affect rows already existing in the table.

Further Explanations

Depending on the type of modification, the MODIFY definition may result in the table having to be recopied and/or indexes rebuilt. Runtime will be considerably long in such cases.

If a table is recopied and the table contains columns marked as deleted, these columns are removed from the database catalog and from the table rows, thus reducing the space requirement for the table.