3.14.1 Specification format and rules for the DROP FOREIGN TABLE statement
Delete a foreign table. If you delete a foreign table, viewed tables that depend on the deleted foreign table are deleted (or invalidated).
- Organization of this subsection
(1) Specification format
DROP FOREIGN TABLE-statement ::= DROP FOREIGN TABLE table-name [drop-behavior]
drop-behavior ::= {CASCADE|RESTRICT}
(2) Explanation of specification format
- ● table-name
-
Specify the name of the foreign table to be deleted. For the 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 base table or viewed table. Also, the table name of an undefined foreign table cannot be specified.
- ● drop-behavior
-
drop-behavior ::= {CASCADE|RESTRICT}Specifies whether or not to delete a foreign table if a viewed table that depends on the foreign table to be deleted is defined. The following table describes the specifications for drop-behavior.
Specification of drop-behavior
Description
Handling of viewed tables that depend on the foreign table to be deleted
If drop-behavior is omitted
Deletes a foreign table even if a viewed table that depends on the foreign table to be deleted is defined.
The viewed tables that depend on the deleted foreign table are invalidated. Not only the viewed table of its own schema, but also those of other schemas will be invalidated.
If CASCADE is specified
The viewed tables that depend on the deleted foreign table are deleted. Not only the viewed table of its own schema, but also those of other schemas will be deleted.
If RESTRICT is specified
If viewed tables that depend on the foreign table to be deleted are defined, the DROP FOREIGN TABLE statement will result in an error.
The dependent viewed tables are not affected because the DROP FOREIGN TABLE statement results in an error.
(3) Privileges required at execution
To execute the DROP FOREIGN TABLE statement, the CONNECT privilege and the schema definition privilege are required.
(4) Rules
-
A user (the HADB user with the authorization identifier that was used for the current connection to the HADB server) can delete only those foreign tables defined by that user. You cannot delete a foreign table defined by another HADB user.
-
If you delete a foreign table, all HADB users who have the access privileges for that foreign table will have the access privileges revoked. Revoking the access privileges might affect viewed tables. For details, see (4) Rules in 3.22.2 Revoking access privileges.
(5) Examples
- Examples
-
Delete the foreign table that is the product browsing history table (ACCESSLOG).
DROP FOREIGN TABLE "ACCESSLOG" CASCADE