Hitachi

Hitachi Advanced Database SQL Reference


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:

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

[Figure] 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

  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.

(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