NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
D-62
Considerations—DROP
You cannot drop a collation that has dependent objects. (See DISPLAY USE OF
Command on page D-51 to learn how to determine dependencies.)
•
Dropping a constraint
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
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
table (including all partitions) and the catalogs of all SQL object program files that
use the table are accessible.
•
Dropping a table or view
You cannot drop a table or view unless all related indexes and views (including all
partitions) are accessible, and unless the catalogs of all SQL object program files
that use the table or view are accessible. You cannot drop a protection view unless
the underlying table (including all partitions and associated indexes) is accessible.
To make sure the object is accessible, perform a LOCK TABLE operation before
issuing the DROP TABLE request.
Dropping a table purges the physical file for the table and drops all dependent
indexes, constraints, partitions, and views. It also invalidates all SQL object
program files that refer to the table. If the dropping process does not have authority
to purge a shorthand view, SQL invalidates the shorthand view.
Dropping a view automatically drops all dependent views, and invalidates all SQL
object program files that refer to the views. If the dropping process does not have
authority to purge a shorthand view, SQL invalidates the shorthand view.
Invalidated shorthand views are unusable and should be dropped by a user with
appropriate security.
WARNING. It is legal to DROP an open table, 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 prior to the DROP TABLE.