Dropping Objects from the Database

---

The DROP statement is used to drop the following objects from the database:

COLLATION	INDEX	SEQUENCE	TABLE
DATABANK	METHOD	SHADOW	TRIGGER
DOMAIN	MODULE	STATEMENT	TYPE
FUNCTION	PROCEDURE	SYNONYM	VIEW
IDENT	SCHEMA

The CASCADE or RESTRICT keywords may be used to specify the action to be taken if other objects exist that are dependent on the object being dropped:

• If RESTRICT (the default) is specified, an error is returned if other objects are affected and the drop operation is aborted.
• If CASCADE is specified, dependent objects are dropped as well.

System database objects can only be dropped by their creator. Private database objects can only be dropped by the creator of the schema to which they belong.

Therefore use caution when using the DROP statement with CASCADE, as the operation may have a recursive effect on all objects relating to it. For example, when a table is dropped, all views, synonyms, routines and triggers created on or referencing that table are also dropped.

The DROP statement removes whole objects from the database. It cannot be used to remove columns from tables, this is done by the ALTER TABLE statement, see Altering Tables.

Dropping Databanks and Tables

Drop the CURRENCIES table:

 DROP TABLE currencies RESTRICT;
 

If the keyword CASCADE is specified, all views, synonyms and indexes based on CURRENCIES are also dropped as well as any functions, procedures and triggers referencing the table.

Drop the MIMER_STORE databank:

 DROP DATABANK mimer_store RESTRICT;
 

If the keyword CASCADE is specified, all tables in the MIMER_STORE databank are also dropped and any views, synonyms, triggers and indexes based on those tables are also dropped as well as any functions, procedures and triggers referencing any of the dropped objects.

An attempt is automatically made to delete the physical databank file when a databank is dropped.

There may be occasions, because of access rights issues in the file system, when the database server's attempt to delete the physical databank file might fail. If recommended procedures for databank file management are followed, see the Mimer SQL System Management Handbook, the databank file should be deleted correctly.

Dropping Sequences

When a sequence is dropped, all the objects (i.e. constraints, domains, functions, procedures, default values, triggers and views) referencing the sequence are also dropped.

Drop the CUSTOMER_ID_SEQ sequence:

 DROP SEQUENCE customer_id_seq CASCADE;
 

The specification of CASCADE ensures that the sequence is dropped even if it is being referenced by other objects in the database.

Dropping Domains

When a domain is dropped, columns using the domain retain the properties of the domain through the creation of column constraints.

Drop the EUROS domain:

 DROP DOMAIN euros CASCADE;
 

Note: If you re-create a domain that has been dropped, the domain will be seen as a completely new domain and it will not be associated with any columns that belonged to the old domain.

To change the restrictions on the columns that were defined with a domain that has been dropped, use the ALTER TABLE statement.

Dropping Idents

When an ident is dropped, everything that the ident has created (including other idents and everything created by those idents) as well as all privileges granted by the ident are dropped. For this reason, physical users should never own objects, except for synonyms and personal views.

Drop the MIMER_ADM ident:

 DROP IDENT mimer_adm RESTRICT;

Dropping Functions, Modules, Procedures, Triggers, User-Defined Types and Methods

The effect of using the keyword CASCADE can be rather dramatic when modules, routines, triggers, user-defined types and methods are dropped. For this reason it is recommended that all those objects are created by running a command file so they can be easily reconstructed in case of being dropped by mistake.

Drop the function called MIMER_STORE_BOOK.FORMAT_ISBN:

 DROP FUNCTION mimer_store_book.format_isbn CASCADE;

Drop the procedure called COMING_SOON:

 DROP PROCEDURE coming_soon CASCADE;

Drop the module called MIMER_STORE_MUSIC.ROUTINES:

 DROP MODULE mimer_store_music.routines CASCADE;

Drop the trigger called PRODUCTS_AFTER_INSERT:

 DROP TRIGGER products_after_insert CASCADE;

Drop a method called AS_FAHRENHEIT for the type TEMPERATURE:

 DROP METHOD as_fahrenheit FOR temperature CASCADE;

About Dropping Modules, Routines, User-Defined Types and Methods

The following points should be noted when dropping modules, routines, user-defined types and methods:

• When a module is dropped, all the routines contained in it will be dropped.
• If a routine, user-defined type, or method is dropped and it is referenced from another object, the referencing object will also be dropped.
• If a routine belonging to a module is to be dropped as a consequence of a cascade, only that routine is dropped (the other routines in the module and the module itself will remain unaffected).

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se