Dropping Objects from the Database
The DROP statement is used to drop the following objects from the database:
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 HOTEL table.DROP TABLE HOTEL RESTRICT;
If the keyword CASCADE is specified, all views, synonyms and indexes based on HOTEL are also dropped as well as any functions, procedures and triggers referencing the table.
Drop the HOTELDB databank:DROP DATABANK HOTELDB RESTRICT;
If the keyword CASCADE is specified, all tables in the HOTELDB 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.
When a sequence is dropped, all the objects (i.e. constraints, domains, functions, procedures, tables, triggers and views) referencing the sequence are also dropped.
Drop the SEQ_1 sequence:DROP SEQUENCE SEQ_1 CASCADE;
The specification of CASCADE ensures that the sequence is dropped even if it is being referenced by other objects in the database.
When a domain is dropped, existing columns assigned the domain retain all the properties of the domain. No new columns may however be assigned the domain.
Drop the BOOK_RATE domain:DROP DOMAIN BOOK_RATE 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.
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 GUEST_CONNECT ident.DROP IDENT GUEST_CONNECT 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 in error.
Drop the function called BILL_TOTAL:DROP FUNCTION BILL_TOTAL CASCADE;
Drop the procedure called ADD_LODGING:DROP PROCEDURE ADD_LODGING CASCADE;
Drop the module called ROOMS_ADMIN:DROP MODULE ROOMS_ADMIN CASCADE;
Drop the trigger called VERIFY_GUEST_UPDATES:DROP TRIGGER VERIFY_GUEST_UPDATES CASCADE;
About Dropping Modules and Routines
The following points should be noted when dropping modules and routines:
- When a module is dropped, all the routines contained in it will be dropped (this is not a cascade effect, but it may provoke cascade effects).
- If a routine 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).
Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40