DROP definition 

You can delete table properties by specifying a DROP definition in the ALTER TABLE statement.

Syntax

<drop_definition> ::= DROP <column_name>,... [<cascade_option>] [RELEASE SPACE]
| DROP (<column_name>,...) [<cascade_option>] [RELEASE SPACE]
| DROP CONSTRAINT <constraint_name>
| DROP FOREIGN KEY <referential_constraint_name>
| DROP PRIMARY KEY

Examples

SQL Tutorial, Tables, Constraints, Foreign Key Dependencies Between Tables, Primary Key

Explanation

DROP <column_name>

You can use DROP <column_name> or DROP (<column_name>,... .) to drop columns.

Each column name must be a column of the table identified by the ALTER TABLE statement. The column must be neither a key column nor a foreign key column of a referential CONSTRAINT definition of the table.

The columns are marked as dropped in the metadata of the table. A DROP definition does not automatically reduce the memory requirements of the underlying table. RELEASE SPACE forces the column values of the dropped columns to be dropped in every row in the table. This may take more time, particularly with longer tables, as a large amount of copying needs to be done.

Any privileges and comments for the columns to be dropped are dropped as well.

If one of the columns to be dropped occurs as a selected column in a view definition, the specified column in the view table is dropped.
If this view table is used in the FROM clause of another view table, the described procedure is applied recursively to this view table.

Existing indexes referring to columns to be dropped are also dropped. The storage locations for the dropped indexes are released.

All CONSTRAINT definitions that contain one of the dropped columns are dropped.

<cascade_option>

·        If one of the columns to be dropped occurs in the QUERY specification of a view definition, and if no CASCADE option or the CASCADE option CASCADE is specified in the DROP definition, the view definition is dropped with all the view tables, privileges, and synonyms that depend on it.

·        If one of the columns to be dropped occurs in the QUERY specification of a view definition, and if the CASCADE condition RESTRICT is specified in the DROP definition, the ALTER TABLE statement fails.

DROP CONSTRAINT <constraint_name>

You can use DROP CONSTRAINT <constraint_name> to drop a constraint.

The constraint name must identify a CONSTRAINT definition in the table. This definition is then deleted from the metadata of the table.

DROP FOREIGN KEY <referential_constraint_name>

You can use DROP FOREIGN KEY <referential_constraint_name> to drop a foreign key.

If DROP FOREIGN KEY was specified, the referential CONSTRAINT definition identified by the name of the referential constraint referential_constraint_name is dropped.

DROP PRIMARY KEY

You can use DROP PRIMARY KEY to delete a key.

·        The table must have a key defined by the user.

·        The table must not contain more than 1023 columns.

·        The maximum permissible length of a row must not exceed 8088 bytes.

·        The key columns must not be referenced columns (referenced_column) of a referential CONSTRAINT definition.

The key is replaced by the key column SYSKEY generated by the database system. This may take more time, particularly with longer tables, as a large amount of copying needs to be done.