MODIFY Clause

Use the MODIFY clause to change the data type, length, or default value of a column, to add or remove the security label of a column, to allow or disallow NULL values in a column, or to reset the serial counter of a SERIAL, SERIAL8, or BIGSERIAL column.

This syntax fragment is part of the ALTER TABLE statement.

MODIFY Clause

Modify Column Clause

Notes:

¹ See Data Type
² Use this path no more than once
³ See DEFAULT clause of ALTER TABLE
⁴ See Single-Column Constraint Format
⁵ See Modify Column Security clause


Element	Description	Restrictions	Syntax
column	Column to modify	Must exist in table. Cannot be a collection or IDSSECURITYLABEL data type.	Identifier

Usage

When you modify a column, all attributes previously associated with the column (that is, default value, single-column check constraint, or referential constraint) are dropped. When you want certain attributes of the column to persist, such as PRIMARY KEY, you must respecify those attributes in the same MODIFY clause.

For example, consider the items table in the stores_demo database, whose schema includes this original definition of the quantity column:

CREATE TABLE items (item_num INT . . . -- primary key of this table
                    order_num    . . . -- foreign key to orders table
                                 . . . -- two additional columns    
                    quantity SMALLINT DEFAULT 1 NOT NULL,
                    total price MONEY(8) . . . );

If you are changing the data type of the existing quantity column to INT, but you want to keep the default value (in this case, 1) and the NOT NULL constraint, you can issue this statement:

ALTER TABLE items MODIFY (quantity INT DEFAULT 1 NOT NULL);

Note: Both the DEFAULT and NOT NULL attributes are specified again in the MODIFY clause. If you omit those keywords, both attributes are dropped from the modified column, and the modified table would accept NULL values in new or updated data rows in which the quantity value is missing.

When you specify a PRIMARY KEY constraint in the MODIFY clause, the database server also silently creates a NOT NULL constraint on the same column, or on the same set of columns that makes up the primary key

When you change the data type of a column, the database server does not perform the modification in place. The next example changes a VARCHAR(15) column to an LVARCHAR(3072) column:

ALTER TABLE stock MODIFY (description LVARCHAR(3072));

When you modify a column that has column constraints that are associated with it, the following constraints are dropped:

All single-column constraints are dropped.
All referential constraints that reference the column are dropped.
If the modified column is part of a multiple-column primary-key or unique constraint, all referential constraints that reference the multiple columns also are dropped.

For example, if you modify a column that has a unique constraint, the unique constraint is dropped. If this column was referenced by columns in other tables, those referential constraints are also dropped. In addition, if the column is part of a multiple-column primary-key or unique constraint, the multiple-column constraints are not dropped, but any referential constraints that are placed on the column by other tables are dropped.

For another example, suppose that a column is part of a multiple-column primary-key constraint. This primary key is referenced by foreign keys in two other tables. When this column is modified, the multiple-column primary-key constraint is not dropped, but the referential constraints that are placed on it by the two other tables are dropped.

Consider the table that this statement defines:

CREATE TABLE tab1(c1 INT, c2 INT);

To add the NOT NULL constraint, an ALTER TABLE MODIFY statement is required:

ALTER TABLE tab1 MODIFY (c1 INT NOT NULL);

You cannot add a NULL or a NOT NULL constraint with the ADD CONSTRAINT clause.

Restrictions on changing column data types

You cannot use the ALTER TABLE MODIFY statement to change the data type of a column to a COLLECTION type or to a ROW type.

If the schema of a table exactly matches the order of data types in the fields of an existing named ROW type, however, you can use the ALTER TABLE ADD TYPE statement to change that table into a typed table, as the topic ADD TYPE Clause describes.

The IDSSECURITYLABEL column of a protected table cannot be altered to a different data type, nor can an existing column be altered to be of type IDSSECURITYLABEL.

In general, the database server does not validate the resulting column values when you change the data types of columns in tables that contain data. The database server does not validate the values when you convert a column from an INTEGER or SMALLINT data type to a SERIAL, SERIAL8, or BIGSERIAL column.