3.12.1 Specification format and rules for the DROP SCHEMA statement
The DROP SCHEMA statement deletes a schema.
Deleting a schema affects tables, indexes, and foreign keys as follows:
-
The tables (base and viewed tables) and indexes that are defined in the schema are also deleted.
-
If viewed tables defined in other schemata depend on the tables that will be deleted by the DROP SCHEMA statement, those dependent viewed tables are also deleted (or invalidated).
-
If the tables that will be deleted by the DROP SCHEMA statement are referenced by foreign keys defined in other schemata, those foreign keys are also deleted.
- Organization of this subsection
(1) Specification format
DROP-SCHEMA-statement ::= DROP SCHEMA [schema-name] [drop-behavior] drop-behavior ::= {CASCADE | RESTRICT}
(2) Explanation of specification format
- schema-name
-
Specifies the name of the schema to be deleted. If the schema name is omitted, the authorization identifier of the HADB user who executed the DROP SCHEMA statement is assumed.
For rules on specifying a schema name, see (1) Schema name specification format in 6.1.5 Qualifying a name.
Note that you cannot specify ALL, HADB, MASTER, or PUBLIC for schema-name.
- • drop-behavior
-
drop-behavior ::= {CASCADE | RESTRICT}
Specifies whether to drop the schema if tables or indexes are defined in the schema to be deleted. The following table describes the specifications for drop-behavior.
Specification of drop-behavior
Description
Handling of viewed tables in other schemata
Handling of foreign keys in other schemata
If drop-behavior is omitted
Even if tables or indexes are defined in the schema to be deleted, the schema is deleted. In this case, any tables or indexes defined in the schema are also deleted.
If viewed tables defined in other schemata depend on the tables that will be deleted by the DROP SCHEMA statement, those dependent viewed tables are invalidated.
If the tables that will be deleted by the DROP SCHEMA statement are referenced by foreign keys defined in other schemata, those foreign keys are also deleted.
If CASCADE is specified
If viewed tables defined in other schemata depend on the tables that will be deleted by the DROP SCHEMA statement, those dependent viewed tables are also deleted.
If RESTRICT is specified
If tables or indexes are defined in the schema to be deleted, the DROP SCHEMA statement results in an error.
The viewed tables in other schemata are not affected because the DROP SCHEMA statement results in an error.
The foreign keys in other schemata are not affected because the DROP SCHEMA statement results in an error.
(3) Privileges required at execution
To execute the DROP SCHEMA statement, the CONNECT privilege and schema definition privilege are required.
(4) Rules
-
You can only delete a schema owned by the current user (the HADB user whose authorization identifier is currently connected to the HADB server). You cannot delete a schema owned by another HADB user. For example, if the adbsql command is executed with ADBUSER01 specified as the authorization identifier, schema ADBUSER01 is the only schema that can be deleted with DROP SCHEMA statement.
-
When you delete a schema, the following cost information is also deleted:
-
The cost information for the tables defined in the schema
-
The cost information for any indexes defined in the schema
-
-
If you delete a schema in which tables are defined, all HADB users who have the access privileges for those tables will have the access privileges revoked. Revoking the access privileges might affect viewed tables and referential constraints. For details, see (4) Rules in 3.17.2 Revoking access privileges.
(5) Examples
- Example 1
-
Delete the schema with schema name ADBUSER01.
DROP SCHEMA "ADBUSER01" CASCADE
- Example 2
-
Delete the schema with schema name ADBUSER01. However, if a table or index has been defined for the schema, make the DROP SCHEMA statement result in an error.
DROP SCHEMA "ADBUSER01" RESTRICT