Show TOC

Background documentationDROP Definition (drop_definition)

 

You can define additional table properties by specifying a DROP definition (drop_definition) in the ALTER TABLE statement.

Structure

Syntax Syntax

  1. <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
End of the code.
Examples

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

DROP <column_name>

You can delete columns using DROP <column_name> or DROP (<column_name>,...).

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 deleted in the metadata of the table. A DROP definition does not automatically reduce the storage requirements of the underlying table. RELEASE SPACE forces the column values of the deleted columns to be deleted in every row in the table. This may take some time, particularly with longer tables, since extensive copy operations are carried out.

Any privileges and comments for the columns to be deleted are also deleted.

If one of the columns to be deleted occurs as a selected column in a view definition, the specified column in the view table is deleted.

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 deleted are also deleted. The storage space for the deleted indexes is made available.

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

<cascade_option>

  • If one of the columns to be deleted 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 deleted with all connected view tables, privileges and synonyms.

  • If one of the columns to be deleted 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 delete a constraint using DROP CONSTRAINT <constraint_name>.

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 delete a foreign key using DROP FOREIGN KEY <referential_constraint_name>.

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

DROP PRIMARY KEY

You can delete a key using DROP PRIMARY 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 some time, particularly with longer tables, since extensive copy operations are carried out.