DROP
The DROP statement deletes an object. Any
objects that are directly or indirectly dependent on that object are also dropped. For example, if you drop a
table, all of the privileges, constraints, and triggers on the table are also dropped. Whenever an object is dropped, its description is deleted
from the catalog. You can drop the following objects:
- Alias
- Distinct type
- Function
- Index
- Package
- Procedure
- Schema
- Table
- Trigger
- View
When dropping a schema, table, type, or view, you can CASCADE or RESTRICT options:
Schema:
- Neither CASCADE nor RESTRICT - Specifies that the
schema will be dropped even if it is referenced in a function, package,
procedure, program, table, or trigger in another schema.
- CASCADE- Specifies that any triggers that reference the schema will be dropped.
- RESTRICT - Specifies that the schema cannot be dropped
if it is referenced in an SQL trigger in another schema.
Table or view:
- Neither CASCADE nor RESTRICT - Specifies that the table or view will be dropped even if
it is referenced in a constraint, index, trigger, or view. All indexes and
views that reference the table or view are dropped.
- CASCADE - Specifies that the table or view will be dropped even if it is referenced
in a constraint, index, trigger, or view.
All constraints, indexes, triggers, and views that reference the table or view are dropped.
- RESTRICT - Specifies that the table or view cannot be dropped
if it is referenced in a constraint, index, trigger, or view.
Type:
- Neither CASCADE nor RESTRICT - Specifies that the
type cannot be dropped if any constraints, indexes, tables, and views
reference the type. Procedures, functions, and triggers that reference the type will be dropped.
- CASCADE - Specifies that the type will dropped even if it is
referenced in a constraint, function, index, procedure, table, trigger, or
view. All constraints, functions, indexes, procedures, tables, triggers, and
views that reference the type are dropped.
- RESTRICT - Specifies that the type cannot be dropped if it is referenced in a constraint,
function (other than a function that was created when the type was created), index, procedure, table,
trigger, or view.
Example:
Drop your table named MY_IN_TRAY. Do not allow the drop
if any views or indexes are created over this table.
DROP TABLE CORPDATA.MY_IN_TRAY RESTRICT
For more information, see DROP in the SQL Reference topic in the
Information Center
.