DROP FOREIGN TABLE (Delete a foreign table)

Function

Deletes the definition of a foreign table without deleting tables on the foreign server side.

Privileges

Owner of a foreign table
This user can delete the definitions of his or her own foreign tables.
Users with the DBA privilege
These users can delete the definitions of foreign tables owned by other users.

Format

DROP FOREIGN TABLE [authorization-identifier.]table-identifier [WITH PROGRAM]

Operands

authorization-identifier
Specifies the authorization identifier of the owner of the foreign table to be deleted. The default is the authorization identifier of the user issuing DROP FOREIGN TABLE.
table-identifier
Specifies the name of the foreign table to be deleted.

When deleting a foreign table, specify this operand to disable functions that use the foreign table in an SQL procedure statement or a valid SQL object in the procedure.

If WITH PROGRAM is omitted, and if there is a function that uses the foreign table or an SQL object with a valid procedure, the foreign table cannot be deleted.

Common rules

  1. Deleting a foreign table also deletes any foreign index, view tables (including public views), comments, and access privileges defined for the foreign table.
  2. If the SQL object being executed becomes nullified, DROP FOREIGN TABLE cannot be executed from a Java procedure.

Notes

  1. DROP FOREIGN TABLE cannot delete tables on the foreign server side.
  2. DROP FOREIGN TABLE cannot be specified from an X/Open compliant UAP running under OLTP.
  3. If a function, or an SQL object with a valid procedure is disabled by specifying WITH PROGRAM, information in the dictionary table SQL_ROUTINE_RESOURCES on any disabled functions and procedures is deleted.
  4. To execute the function or procedure that was disabled by specifying WITH PROGRAM, it is necessary to recreate the function or the SQL object for the procedure by executing either ALTER ROUTINE or ALTER PROCEDURE.