3.12.1 Specification format and rules for the DROP SCHEMA statement

(1) Specification format

DROP-SCHEMA-statement ::= DROP SCHEMA [schema-name] [drop-behavior]
 
  drop-behavior ::= {CASCADE | RESTRICT}

To Page Top

(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.

To Page Top

(3) Privileges required at execution

To execute the DROP SCHEMA statement, the CONNECT privilege and schema definition privilege are required.

To Page Top

(4) Rules

1. 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.

2. 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

3. 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.

To Page Top

(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

To Page Top