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:
DROP TABLE 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:
DROP TABLE HIERARCHY person
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.