|
|
|
|
|
|
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 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, tables, 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, existing columns assigned the domain retain all the properties of the domain. No new columns may however be assigned the domain.
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 in error.
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:
- 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 dbtechnology@upright.se |
|
|
|
|
|
|