DB2 Version 9.7 for Linux, UNIX, and Windows

Dropping tables

A table can be dropped with a DROP TABLE statement. When a table is dropped, the row in the SYSCAT.TABLES system catalog view that contains information about that table is dropped, and any other objects that depend on the table are affected.

About this task

For example:
  • All column names are dropped.
  • Indexes created on any columns of the table are dropped.
  • All views based on the table are marked inoperative.
  • All privileges on the dropped table and dependent views are implicitly revoked.
  • All referential constraints in which the table is a parent or dependent are dropped.
  • All packages and cached dynamic SQL and XQuery statements dependent on the dropped table are marked invalid, and remain so until the dependent objects are re-created. This includes packages dependent on any supertable above the subtable in the hierarchy that is being dropped.
  • Any reference columns for which the dropped table is defined as the scope of the reference become "unscoped".
  • An alias definition on the table is not affected, because an alias can be undefined
  • All triggers dependent on the dropped table are marked inoperative.
To drop a table using the command line, enter:
   DROP TABLE <table_name>
The following statement drops the table called DEPARTMENT:
An individual table cannot be dropped if it has a subtable. However, all the tables in a table hierarchy can be dropped by a single DROP TABLE HIERARCHY statement, as in the following example:

The DROP TABLE HIERARCHY statement must name the root table of the hierarchy to be dropped.

There are differences when dropping a table hierarchy compared to dropping a specific table:
  • DROP TABLE HIERARCHY does not activate deletion-triggers that would be activated by individual DROP table statements. For example, dropping an individual subtable would activate deletion-triggers on its supertables.
  • DROP TABLE HIERARCHY does not make log entries for the individual rows of the dropped tables. Instead, the dropping of the hierarchy is logged as a single event.