Oracle7 Server SQL Reference

DROP clause

Purpose

To remove an integrity constraint from the database.

Prerequisites

The DROP clause can appear in an ALTER TABLE statement. To drop an integrity constraint, you must have the privileges necessary to issue an ALTER TABLE statement. For information on these privileges, see the ALTER TABLE command

Syntax

Keywords and Parameters

PRIMARY KEY

drops the table's PRIMARY KEY constraint.

UNIQUE

drops the UNIQUE constraint on the specified columns.

CONSTRAINT

drops the integrity constraint named constraint.

CASCADE

drops all other integrity constraints that depend on the dropped integrity constraint.

Usage Notes

You can drop an integrity constraint by naming it in a DROP clause of an ALTER TABLE statement. When you drop an integrity constraint, Oracle7 stops enforcing the integrity constraint and removes it from the data dictionary.

You cannot drop a unique or primary key that is part of a referential integrity constraint without also dropping the foreign key. You can drop the referenced key and the foreign key together by specifying the referenced key with the CASCADE option in the DROP clause.

Example I

The following statement drops the primary key of the DEPT table:

ALTER TABLE dept

	DROP PRIMARY KEY CASCADE

If you know that the name of the PRIMARY KEY constraint is PK_DEPT, you could also drop it with the following statement:

ALTER TABLE dept

	DROP CONSTRAINT pk_dept CASCADE

The CASCADE option drops any foreign keys that reference the primary key.

Example II

The following statement drops the unique key on the DNAME column of the DEPT table:

ALTER TABLE dept

	DROP UNIQUE (dname)