DROP

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.

Restrictions

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

A system 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. Currently, neither RESTRICT nor CASCADE is supported. A statement will never cause any cascading effects to occur.

Notes

Collation

Databank

When a databank is dropped, all tables in the databank are deleted. 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).

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.

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.

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.)

Module

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

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.

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.

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.

Shadow

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.

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.

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.

Statement

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.

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.

Comment

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

Example

Standard Compliance


Standard	Compliance	Comments
SQL-2003	Core	Fully compliant.
SQL-2003	Features outside core	Feature F032, "CASCADE drop behavior" support for the cascade option. Feature F251, "Domain support" support for drop domain statement. Feature F691, "Collations and translations" support for drop collation statement. Feature T211, "Basic trigger capability" support for drop trigger statement.
	Mimer SQL extension	DROP DATABANK, DROP IDENT, DROP INDEX, DROP SEQUENCE, DROP STATEMENT, DROP SHADOW and DROP SYNONYM are Mimer SQL extensions. Optional CASCADE or RESTRICT is a Mimer SQL extension.