Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


Dropping Objects from the Database


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

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

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.

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 RESTRICT;
 
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 and Triggers

The effect of using the keyword CASCADE can be rather dramatic when modules, routines and triggers are dropped. For this reason it is recommended that all modules, routines and triggers be 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;

About Dropping Modules and Routines

The following points should be noted when dropping modules and routines:


Mimer
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se
Mimer SQL Documentation TOC PREV NEXT INDEX