SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Statements

HP NonStop SQL/MX Reference Manual—540440-003

2-127

DROP TABLE Statement

Considerations for DROP TABLE

Examples of DROP TABLE

The DROP TABLE statement deletes an SQL/MX table and any indexes, constraints,

and inactive locks on the table. See Database Object Names on page 6-12.

Syntax Description of DROP TABLE

table

is the name of the table to delete. If the table has an active DDL lock, neither the

table nor any of its dependent objects are dropped. 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.

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 is

released when the utility locking the file completes.

The default is RESTRICT.

Considerations for DROP TABLE

Restrictions

You can drop a table with partitions, but you cannot drop individual partitions within a

table with the DROP TABLE statement. However, you can drop these partitions by

using the MODIFY utility. See MODIFY Utility on page 5-47.

You cannot drop an SQL/MP table by using its SQL/MP alias name.

Authorization and Availability Requirements

To drop a table, you must own the schema that contains the table or be the super ID.

Recovery

When a table is dropped, NonStop SQL/MX automatically saves the DDL needed to re-

create the table in an OSS file. If you do not want to save this text, set the

SAVE_DROPPED_TABLE_DDL control query to "OFF".

The DDL is saved so that you can later retrieve it if you need to re-create the dropped

table for any reason. If the table needs to be recovered with TMF or re-created for use

DROP TABLE table [RESTRICT | CASCADE]