DROP TABLE (Delete table)

Function

DROP TABLE deletes a table.

Privileges

Owner of the table

The owner of a table can delete that table.

Users with the DBA privilege

A user with the DBA privilege can delete tables owned by other users.

Format

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

Operands

authorization-identifier
Specifies the authorization identifier of the user who owns the table.
When this operand is omitted, the authorization identifier of the user who is executing the command is assumed.
table-identifier
Specifies the name of the table to be deleted.
WITH PROGRAM
Specify this operand when deleting a table and when disabling any function that uses the table in an SQL procedure statement, a procedure, or an SQL object with a trigger that is in effect.
If WITH PROGRAM is omitted, the table cannot be deleted if there is a function, a procedure, or an SQL object with an effective trigger that uses the table (exclusive of triggers for performing a referential constraint operation that are internally defined for the referenced table referenced by the table being deleted).
If a referencing table is deleted, any of the functions, procedures, and SQL objects with a trigger that is in effect shown in the following table are disabled:

Table 3-44 Disabled objects

Version in which the object was createdDescription of object
Object typeNullifying condition
07-00 or laterFunction, procedure, or trigger objectThe object contains either an UPDATE or DELETE statement that uses a referenced table referenced by the referencing table.
Before 07-00The object contains an SQL statement that uses a referenced table referenced by the referencing table.

Common rules

  1. Deleting a table also causes any indexes, view tables (including public views), comments, access privileges, and triggers defined for the table to be deleted.
    If a referencing table is deleted, any triggers for performing referential constraint operations internally defined on the referenced table, are also deleted.
  2. DROP TABLE cannot be executed from within a Java procedure if the deletion results in invalidation of the SQL object being executed.
  3. If tables, indexes defined for a table, and columns of the abstract data type containing LOB columns and LOB attributes that are defined for a table are stored in an RDAREA to which the inner replica facility is applied, such items can be deleted, provided that certain conditions are met. For details about execution conditions for DROP TABLE under a condition in which the inner replica facility is used, see the manual HiRDB Version 9 Staticizer Option.
  4. If the frozen update specification is specified for a LOB column defined for a table or for an RDAREA that stores columns of the LOB attribute, that table cannot be deleted.
  5. If a specified table is a falsification-prevented table and the falsification-prevented table contains rows, that table cannot be deleted.
  6. Users with the DBA privilege can delete tables owned by other users, but not audit trail tables owned by an auditor.
  7. Referenced tables that are referenced by a foreign key cannot be deleted.

Notes

  1. The DROP TABLE statement cannot be specified from an X/Open-compliant UAP running under OLTP.
  2. If an SQL object for which functions, procedures, or triggers are in effect is nullified by specifying WITH PROGRAM, any information associated with the nullified functions, procedures, or triggers in the SQL_ROUTINE_RESOURCES dictionary table is deleted.
  3. Before executing the SQL object associated with the function, procedure, or trigger that was nullified by specifying WITH PROGRAM, you need to re-create the function, procedure, or trigger by executing ALTER ROUTINE, ALTER PROCEDURE, or ALTER TRIGGER.
  4. To execute the SQL object associated with the trigger that was nullified by specifying WITH PROGRAM, you need to perform either of the following operations:
    • Redefine the table, and re-create the trigger SQL object by executing either ALTER TRIGGER or ALTER ROUTINE.
    • Delete the nullified trigger by using DROP TRIGGER, and then redefine the trigger by using CREATE TRIGGER so that the deleted tables are not reused. If triggers satisfying all of the following conditions exist, use DROP TRIGGER to delete them all, and redefine the triggers using CREATE TRIGGER in the order in which they were defined so that there is no change in the sequence of execution of trigger actions:
      Conditions:
      [Figure]The defined trigger is later than the nullified trigger.
      [Figure]The nullified trigger is the same as the defined table.
      [Figure]The nullified trigger is the same as the trigger event (INSERT, UPDATE, or DELETE) (for UPDATE, the nullified trigger is considered to be the same as the trigger event regardless of whether a trigger event column is specified or the contents of the specification).
      [Figure]The nullified trigger has the same trigger action timing (BEFORE or AFTER) as the trigger event.
      [Figure]The nullified trigger has the same trigger action units (units of rows or statements) as the trigger event.

Example

Delete a stock table (STOCK):

DROP TABLE STOCK