SQL/MX 2.x Installation and Management Guide (G06.24+, H06.03+)

Adding, Altering, and Dropping SQL/MX Database
Objects
HP NonStop SQL/MX Installation and Management Guide—523723-004
9-36
Dropping Tables
Dropping Tables
To remove a table and its dependent objects from the database, use the DROP TABLE
statement. Use the PURGEDATA utility to remove only the data from a table and its
dependent objects and leave the objects intact.
For more information, see the SQL/MX Reference Manual.
Dropping Tables and Their Data
To drop a table and its data, use the DROP TABLE statement. Dropping a base table
with dependencies is essentially dropping each of the dependent objects (indexes,
constraints) separately. SQL drops all the dependencies automatically.
If the table contains references to another object (for example, an RI constraint), the
drop operation fails if RESTRICT is specified. The drop operation should succeed if
you use the CASCADE option to drop these dependent objects.
Guidelines for Dropping a Table and its Data
•
If you plan to use the TMF subsystem to recover an SQL/MX table, see
Recovering Tables on page 12-8.
•
To have the authority to drop a table, you must have all the security and authority
required to drop or invalidate all dependent objects, including access to all the
catalogs describing all the dependent objects.
•
You can use the DROP TABLE statement to drop a table with partitions, but not to
drop individual partitions within a table. For information about using the MODIFY
utility to drop the individual partitions of a table, see Dropping Partitions on
page 9-32
•
When you drop a table, the operation invalidates the programs that depend on that
table. To avoid expensive automatic recompilations of these programs, you should
change them as necessary and recompile them explicitly, as in Steps for Dropping
a Table and its Data on page 9-37.
•
A table that has an active DDL lock (one for which the process that created it still
exists) cannot be dropped even if you specify CASCADE. An active DDL lock will
be released when the utility locking the file completes.
•
If you specify RESTRICT and table is referenced by a view, a trigger, or a
referential constraint of another table, or if the table has an active DDL lock, the
specified table cannot be dropped.
•
If you specify CASCADE, the table and all of its views, triggers, referential
constraints, and inactive DDL locks are dropped.
For more information about the DROP TABLE statement, see the SQL/MX Reference
Manual.