DROP

Drops an object from the database.

 

where routine-specification is:

 

Usage

Embedded, Interactive, Module, ODBC, JDBC.

Description

The named object is deleted from the database. The object name is free to be reused for other objects.

The CASCADE and RESTRICT keywords specify the action to be taken if other objects exist that are dependent on the object being dropped. If CASCADE is specified, such objects will be dropped as well. If RESTRICT is specified, an error is returned if other objects are affected, and no objects are dropped.

If neither RESTRICT nor CASCADE is specified, then RESTRICT is implicit.

Restrictions

A database object can only be dropped by its creator, unless it is implicitly dropped because of cascade effects when another object is dropped, see the Notes section below.

You must have exclusive use of a table to drop the table or an index on the table, and of a databank to drop the databank.

DROP SHADOW is only for use with the optional Mimer SQL Shadowing module, and requires SHADOW privilege.

The databank for which the shadow exists cannot be used by any other user while the shadow is being dropped.

Only the creator of a STATEMENT can drop it. Neither RESTRICT nor CASCADE is supported, since DROP STATEMENT will never cause any cascading effects to occur.

Notes

DROP COLLATION

You can drop a collation only if there are no dependencies.

DROP COMMENT

Comments may not be dropped from the data dictionary, but they may be replaced by blank comments, see COMMENT.

DROP DATABANK

When a databank is dropped, all tables and sequences in the databank are dropped. All shadows defined on the databank are also dropped. An attempt is made to delete the physical file in which the databank is stored. If the file deletion is unsuccessful for any reason (e.g. the disk is not mounted), the databank is dropped from the database but the file remains.

If the databank is OFFLINE, no attempt is made to delete the physical databank file or any shadow file(s).

DROP DOMAIN

When a domain is dropped, existing columns defined using the domain retain all the properties of the domain. No new columns may however use the domain. All routines, triggers or views whose definitions contain a CAST involving the domain will be dropped.

DROP FUNCTION

When a function is dropped with the CASCADE option in effect, all constraints, functions, procedures, triggers or views invoking it will be dropped. Dropping any object referenced from the SQL statements in the body of a function will drop the function when the CASCADE option is in effect.

DROP IDENT

When an ident is dropped, all objects owned by the ident are dropped, and all privileges granted by the ident are revoked. (Remember that revocation of privileges, in particular, may have recursive effects on other objects.)

DROP INDEX

When an index is dropped and CASCADE is in effect, all the objects (i.e. functions, procedures, statements, triggers and views) explicitly referencing the index are also dropped.

DROP MODULE

When a module is dropped, all the routines belonging to the module are also dropped.

DROP PROCEDURE

When a procedure is dropped with the CASCADE option in effect, all other routines or triggers calling it will be dropped. Dropping any object referenced from the SQL statements in the body of a procedure will drop the procedure when the CASCADE option is in effect.

DROP SCHEMA

When a schema is dropped and CASCADE is in effect, all the objects belonging to the schema are also dropped. If RESTRICT is in effect, the schema will be dropped only if it is empty.

DROP SEQUENCE

When a sequence is dropped and CASCADE is in effect, all the objects (i.e. domains, functions, procedures, table columns, triggers and views) referencing the sequence are also dropped.

DROP SHADOW

DROP SHADOW deletes the named shadow from the data dictionary.

An attempt is made to delete the physical shadow file in the same way as for dropping a databank. If the shadow or the master databank is OFFLINE however, no attempt is made to delete the physical shadow file.

DROP STATEMENT

A statement may not be dropped when it is in use.

DROP SYNONYM

There are no cascade effects when a synonym is dropped because it is resolved to the associated table or view when an SQL statement containing the synonym is executed. Thus, it is a table or view reference that is actually stored in the database, not the synonym reference. Once dropped, of course, the synonym can no longer be used in new SQL statements.

DROP TABLE

When a table is dropped, all views based on that table and all triggers created on it are also dropped.

When a table referenced from within a routine, trigger or statement is dropped with the CASCADE option in effect, the routine, trigger or statement will also be dropped, see also the notes above for Function, Module, Procedure and Trigger for full cascade implications.

If a table used as a REFERENCES table in a FOREIGN KEY clause is dropped, the referential integrity constraint is lost from the table with the foreign key clause.

All cursors defined for a table must be closed before the table can be dropped.

DROP TRIGGER

If a trigger has been created on a non-updatable view, the creator of the trigger implicitly gets the appropriate privilege for the trigger event on that view, with WITH GRANT OPTION.

The creator of the trigger may then have granted the privilege to other idents or may have used the privilege to perform updates on the view in one or more routines subsequently created.

If the trigger is then dropped, with the CASCADE option in effect, any routines using the privilege to update the view will be dropped and the privilege will be revoked from any idents to whom the trigger creator granted it.

DROP VIEW

When a view is dropped, all other views based on that view and all triggers created on it are also dropped.

When a view referenced from within a routine, trigger or statement is dropped with the CASCADE option in effect, the routine, trigger or statement will also be dropped, see also the notes above for Function, Procedure and Module for full cascade implications.

Example

DROP IDENT joe CASCADE;

Standard Compliance