MODIFY Definition (modify_definition) 

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

Syntax

<modify_definition> ::= MODIFY (<modify_column_definition>,...)

<modify_column_definition> ::=
  <column_name> <data_type> <column_attributes>
| <column_name> <data_type>
| <column_name> <column_attributes>

Examples

SQL Tutorial, Tables

Explanation

The parentheses are not necessary if the MODIFY definition only contains one column name (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 legal search condition once the data type has been modified.

<data_type>

If no DEFAULT specification is specified and a DEFAULT specification is defined for the corresponding column, this must be compatible with the data type (data_type).

●     Code attribute ASCII: The corresponding column must have the data type DATE, TIME, or TIMESTAMP or the code attribute ASCII or UNICODE before it is modified.

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

●     Code attribute BYTE: The corresponding column must have the data type DATE, TIME, or TIMESTAMP or the code attribute ASCII or BYTE before it is modified.

Data type CHAR(n), VARCHAR(n): The corresponding column must have 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 is always possible.

Data type DATE: The corresponding column must have 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 have 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 have the data type FIXED(n,m), FLOAT(n), INT, or SMALLINT.

Data type INT: The corresponding column must have 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 have 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 have 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 have the data type CHAR(n), VARCHAR(n), or TIMESTAMP. This column must contain a timestamp value in any of the timestamp 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 valuesapurl_link_0004_0017_0018

●     NOT NULL
If 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.

Additional Information

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.