Deletes a schema and all the definitions that it contains.
1 – Environment
You can use the DROP SCHEMA statement:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
2 – Format
(B)0[m[1;4mDROP[m[1m [1;4mSCHEMA[m[1m <schema-name> qwqwqqqqqqqqqqqqqqqwqwqq>[m
[1m [m [1mx tq> [1;4mCASCADE[m[1m [m [1mqqqu[m [1mx [m
[1m [m [1mx tq> [1;4mRESTRICT[m [1mqqu[m [1mx [m
[1mx[m [1mmq> [1;4mIF[m[1m [1;4mEXISTS[m[1m qqj[m [1mx[m
[1m [m [1mmqqqqqqqqqq<qqqqqqqqj[m
3 – Arguments
3.1 – CASCADE
Deletes all other definitions (views, constraints, tables,
sequences, indexes, and triggers) that refer to the named schema
and then deletes that schema definition. This is known as a
cascading delete.
If you specify the CASCADE keyword, SQL deletes all definitions
contained by the schema before deleting the schema.
If you do not specify the CASCADE keyword, the schema must be
empty.
3.2 – IF_EXISTS
Prevents SQL command language from displaying error messages if
the referenced object does not exist in the database.
3.3 – RESTRICT
Returns an error message if other definitions refer to the named
schema. The DROP SCHEMA RESTRICT statement will not delete a
schema until you have deleted all other definitions that refer to
the named schema. The DROP SCHEMA statement specifies an implicit
RESTRICT by default.
3.4 – schema-name
Specifies the schema name. You must qualify the schema name with
catalog and alias names if the schema is not in the default
catalog and database. For more information about schema names,
see the User_Supplied_Names HELP topic.
4 – Examples
Example 1: Deleting a schema with implicit RESTRICT
In the following example, the user must delete the definitions
that refer to the schema RECRUITING before deleting the schema
itself.
After setting the default schema to RECRUITING and the default
catalog to ADMINISTRATION, the user can qualify each definition
name with only the alias CORP.
SQL> ATTACH 'ALIAS CORP FILENAME CORPORATE_DATA';
SQL> SET CATALOG '"CORP.ADMINISTRATION"';
SQL> SET SCHEMA '"CORP.ADMINISTRATION".RECRUITING';
SQL> SET QUOTING RULES 'SQL92';
SQL> DROP SCHEMA "CORP.RECRUITING";
%RDB-E-NO_META_UPDATE, metadata update failed
-RDMS-E-SCHEMAINUSE, schema RECRUITING currently in use
SQL> DROP TABLE "CORP.CANDIDATES";
SQL> DROP TABLE "CORP.COLLEGES";
%RDB-E-NO_META_UPDATE, metadata update failed
-RDMS-F-CONEXI, relation COLLEGES is referenced in constraint DEGREES_FOREIGN3
-RDMS-F-RELNOTDEL, relation COLLEGES has not been deleted
SQL> DROP TABLE "CORP.DEGREES";
%RDB-E-NO_META_UPDATE, metadata update failed
-RDMS-F-TRGEXI, relation DEGREES is referenced in trigger
EMPLOYEE_ID_CASCADE_DELETE
SQL> DROP TABLE "CORP.RESUMES";
%RDB-E-NO_META_UPDATE, metadata update failed
-RDMS-F-TRGEXI, relation RESUMES is referenced in trigger
EMPLOYEE_ID_CASCADE_DELETE
-RDMS-F-RELNOTDEL, relation RESUMES has not been deleted
SQL> --
SQL> -- The trigger is part of another schema, PERSONNEL. Since this
SQL> -- is not the default schema, the user qualifies the trigger name
SQL> -- with schema and names.
SQL> --
SQL> DROP TRIGGER "CORP.ADMINSTRATION".PERSONNEL.EMPLOYEE_ID_CASCADE_DELETE;
SQL> DROP CONSTRAINT "CORP.DEGREES_FOREIGN3";
%RDB-E-NO_META_UPDATE, metadata update failed
-RDMS-F-CONDELVIAREL, constraint DEGREES_FOREIGN3 can only be deleted by
changing or deleting relation DEGREES
SQL> DROP TABLE "CORP.DEGREES";
SQL> DROP TABLE "CORP.RESUMES";
SQL> DROP TABLE "CORP.COLLEGES";
SQL> DROP SCHEMA "CORP.RECRUITING";
Example 2: Deleting a schema with CASCADE
In the following example, SQL deletes the definitions that refer
to the schema ACCOUNTING, then deletes the schema itself:
SQL> DROP SCHEMA "CORP.ACCOUNTING" CASCADE;
Domain "CORP.ADMINISTRATION".ACCOUNTING.BUDGET is also being dropped.
Domain "CORP.ADMINISTRATION".ACCOUNTING.CODE is also being dropped.
SQL>