Drops an object from the database.
The named object is deleted from the database. The object name is free to be reused for other objects.
RESTRICTkeywords specify the action to be taken if other objects exist that are dependent on the object being dropped. If
CASCADEis specified, such objects will be dropped as well. If
RESTRICTis specified, an error is returned if other objects are affected, and no objects are dropped.
CASCADEis specified, then
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 SHADOWis only for use with the optional Mimer SQL Shadowing module and requires
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
STATEMENTcan drop it. Currently, neither
CASCADEis supported. A statement will never cause any cascading effects to occur.
You can drop a collation only if there are no dependencies.
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).
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
CASTinvolving the domain will be dropped.
When a function is dropped with the
CASCADEoption in effect, all constraints, functions, methods, 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
CASCADEoption is in effect.
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.)
When a method is dropped with the
CASCADEoption in effect, all other routines or triggers using it will be dropped. Dropping the user-defined type the method is associated with, or dropping any object referenced from the SQL statements in the body of a method, will drop the method when the
CASCADEoption is in effect.
When a module is dropped, all the routines belonging to the module are also dropped.
When a procedure is dropped with the
CASCADEoption 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
CASCADEoption is in effect.
When a schema is dropped and
CASCADEis in effect, all the objects belonging to the schema are also dropped. If
RESTRICTis in effect, the schema will be dropped only if it is empty.
When a sequence is dropped and
CASCADEis in effect, all the objects (i.e. domains, functions, procedures, table columns, triggers and views) referencing the sequence are also dropped.
DROP SHADOWdeletes 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
OFFLINEhowever, no attempt is made to delete the physical shadow file.
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.
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
CASCADEoption in effect, the routine, trigger or statement will also be dropped, see also the notes above for Function, Method, Module, Procedure and Trigger for full cascade implications.
If a table used as a
REFERENCEStable in a
FOREIGN KEYclause 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.
A statement may not be dropped when it is in use.
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
CASCADEoption 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.
When a user-defined type is dropped, with the
CASCADEoption in effect, all columns defined using the type will be dropped (as long as it's not the last column of a table.) Also all routines, triggers and views using the type will be dropped.
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
CASCADEoption in effect, the routine, trigger or statement will also be dropped, see also the notes above for Function, Method, Procedure and Module for full cascade implications.
Comments may not be dropped from the data dictionary, but they may be replaced by blank comments, see COMMENT.
ExampleDROP IDENT joe CASCADE;
SQL-2011 Core Fully compliant. SQL-2011 Features outside core Feature F032, "CASCADE drop behavior" support for the cascade option.Feature F251, "Domain support" support for drop domain statement.Feature F690, "Collation support" 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 STATEMENT, DROP SHADOW, and DROP SYNONYM are Mimer SQL extensions.Optional CASCADE or RESTRICT is a Mimer SQL extension.
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40