3.13.1 Specification format and rules for the DROP TABLE statement

(1) Specification format

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

To Page Top

(2) Explanation of specification format

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.

To Page Top

(3) Privileges required at execution

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

To Page Top

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

To Page Top

(5) Examples

Example

Delete the shops table (SHOPSLIST).

DROP TABLE "SHOPSLIST" CASCADE

To Page Top