SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
D-64
Considerations—DROP
because of the drop. In addition, you cannot drop an object until after the time and
date specified for the NOPURGEUNTIL attribute of the object.
To drop a table, view, or program, you must also have authority to purge the object
being dropped. To drop a partitioned object, all partitions must be accessible.
Additional requirements for dropping other types of objects are described later in
this entry.
Only one DDL statement can operate on a given SQL object (or partition of an SQL
object) at a time. An error occurs if you attempt to execute a DROP statement
while another process is executing a DDL operation on the same object. The
specific error depends on the DDL operation involved and the phase of the
operation at which the conflict occurs. For information, see DDL (Data Definition
Language) Statements on page D-20.

You cannot drop a catalog unless you first drop all user tables, views (except for
views defined on catalog tables), indexes, collations, and program files from the
catalog. To drop a catalog, you must have authority to read and purge the catalog
tables, and authority to read and write to SQL.CATALOGS.

You cannot drop a collation that has dependent objects. To learn how to determine
dependencies, see DISPLAY USE OF Command on page D-54.

Dropping a constraint invalidates all SQL object program files that use the
underlying table and can change the version of the associated table and any views
defined on that table.
To drop a constraint, you must be the local or remote owner of the underlying table,
with purge authority, or the local super ID. You cannot drop a constraint unless the
underlying table (including all partitions) and the catalogs of all SQL object program
files that use the underlying table are accessible.

Dropping an index purges the physical file for the index, including all its partitions.
It also invalidates all SQL object program files that use the table underlying the
index and it can change the version of the table and any views defined on the
table.
To drop an index, you must be the local or remote owner of the underlying table,
with purge authority, or the local super ID. You cannot drop an index unless the
Caution. Using the DROP statement to delete a table is permitted if you have PURGE
authority, there are no locks outstanding on the table, and if you are beyond the
NOPURGEUNTIL date.
To adequately protect important tables, use the NOPURGEUNTIL attribute, specifying a date
well into the future, or change PURGE authority to “-” (SUPER only). Either method requires an
ALTER TABLE before the DROP TABLE.