Hitachi

Hitachi Advanced Database SQL Reference


3.13.1 Specification format and rules for the DROP TABLE statement

The DROP TABLE statement deletes a base table.

Deleting a base table affects indexes, table constraints, and viewed tables as follows:

#

Primary keys, and any foreign keys defined in the table to be deleted, are deleted. The foreign keys that reference the deletion-target table are also deleted (even if the foreign keys are defined in other schemata).

Organization of this subsection

(1) Specification format

DROP-TABLE-statement ::= DROP TABLE table-name [drop-behavior]
 
 
 drop-behavior ::= {CASCADE | RESTRICT}

(2) Explanation of specification format

[Figure] table-name

Specifies the name of the base table to be deleted. For rules on specifying a table name, see (2) Table name specification format in 6.1.5 Qualifying a name.

Note that you cannot specify the name of a viewed table.

drop-behavior
drop-behavior ::= {CASCADE | RESTRICT}

Specifies whether to delete the base table if any of the following conditions are met:

  • There is an index defined for the base table to be deleted.

  • Viewed tables that depend on the base table to be deleted are defined.

  • A table constraint has been defined for the base table to be deleted.

The following table describes the specifications for drop-behavior.

Specification of drop-behavior

Description

Handling of viewed tables that depend on the base table to be deleted

If drop-behavior is omitted

The base table is also deleted if any of the following conditions are met:

  • There is an index defined for the base table to be deleted.

  • Viewed tables that depend on the base table to be deleted are defined.

  • A table constraint has been defined for the base table to be deleted.

In this case, the following items are also deleted:

  • Indexes and table constraints defined in the base table

  • Foreign keys that reference the deletion-target table (including those in other schemata)

The viewed tables that depend on the deleted base table are invalidated. Not only the viewed table in the relevant schema, but also the dependent viewed tables in other schemata, are invalidated.

If CASCADE is specified

The viewed tables that depend on the deleted base table are deleted. Not only the viewed table in the relevant schema, but also the dependent viewed tables in other schemata, are deleted.

If RESTRICT is specified

If any of the following conditions are met, the DROP TABLE statement results in an error.

  • There is an index defined for the base table to be deleted.

  • Viewed tables that depend on the base table to be deleted are defined.

  • A table constraint has been defined for the base table to be deleted.

The dependent viewed tables are not affected because the DROP TABLE statement results in an error.

(3) Privileges required at execution

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

(4) Rules

  1. The DROP TABLE statement can be used to drop only base tables owned by the current user (the HADB user whose authorization identifier is currently connected to the HADB server). You cannot delete a base table owned by another HADB user.

  2. Base tables with data stored in them can be deleted.

  3. You cannot delete the base tables of a dictionary table or system table.

  4. When you delete a table, the following cost information is also deleted:

    • The cost information for the table

    • The cost information for any indexes defined for the table

  5. If you delete a table, all HADB users who have the access privileges for that table 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.

  6. When an archivable multi-chunk table is deleted, the following table and indexes are also deleted:

    • Range index automatically defined for the archive range column

    • Location table

    • Location table index

  7. If an archivable multi-chunk table is deleted, data stored in chunks (either archived or not) is deleted.

(5) Examples

Example

Delete the shops table (SHOPSLIST).

DROP TABLE "SHOPSLIST" CASCADE