Removes objects from the database.
Syntax
DROP
{ DBSPACE <dbspace-name>
| { DATATYPE [ IF EXISTS ]
| DOMAIN } <datatype-name>
| EVENT [ IF EXISTS ] <event-name>
| INDEX [ IF EXISTS ] [ [ <owner>].<table-name>.]<index-name>
| MESSAGE <message-number>
| TABLE [ IF EXISTS ] [ <owner>.]<table-name>
| VIEW [ IF EXISTS ] [ <owner>.]<view-name>
| MATERIALIZED VIEW [ IF EXISTS ] [ <owner>.]<view-name>
| PROCEDURE [ IF EXISTS ] [ <owner>.]<procedure-name>
| FUNCTION [ IF EXISTS ] [ <owner>.]<function-name> }
Parameters
(back to top)
- DBSPACE DROP DBSPACE is prevented whenever the
statement affects a table that is currently being used by another
connection.
- IF EXISTS use
if you do not want an error returned when the DROP statement attempts to remove a database object that does
not exist.
- INDEX DROP INDEX deletes any explicitly created index. It deletes an
implicitly created index only if there are no unique or foreign-key
constraints or associated primary key.
DROP INDEX is
prevented whenever the statement affects a table that is currently being
used by another connection.
For a nonunique HG index,
DROP INDEX fails if an associated unenforced
foreign key exists.
Caution Do not delete views owned by the
DBO user. Deleting such views or changing them into tables might
cause problems.
- TABLE DROP TABLE is prevented whenever the statement affects a table
that is currently being used by another connection or if the primary table
has foreign-key constraints associated with it, including unenforced
foreign-key constraints. It is also prevented if the table has an IDENTITY
column and IDENTITY_INSERT is set to that table. To drop the table you must
clear IDENTITY_INSERT, that is, set IDENTITY_INSERT to ' ' (an empty
string), or set to another table name.
A foreign key can have either a
nonunique single or a multicolumn HG index. A primary key may have
unique single or multicolumn HG indexes. You cannot drop the HG index
implicitly created for an existing foreign key, primary key, and unique
constraint.
The four initial dbspaces are SYSTEM,
IQ_SYSTEM_MAIN, IQ_SYSTEM_TEMP,
and IQ_SYSTEM_MSG. You cannot drop these initial
dbspaces, but you may drop dbspaces from the IQ main store or catalog
store, which may contain multiple dbspaces, as long as at least one
dbspace remains with readwrite mode.
You must drop tables in the
dbspace before you can drop the dbspace. An error is returned if the
dbspace still contains user data; other structures are automatically
relocated when the dbspace is dropped. You can drop a dbspace only after
you make it read-only.
Note A dbspace may contain data
at any point after it is used by a command, thereby preventing a
DROP DBSPACE on it.
In a multiplex,
dropping of RLV-enabled tables is only allowed on the
coordinator.
- PROCEDURE DROP
PROCEDURE is prevented when the procedure is in use by another
connection.
- DATATYPE DROP
DATATYPE is prevented if the data type is used in a table. You
must change data types on all columns defined on the user-defined data type
to drop the data type. It is recommended that you use DROP DOMAIN rather than DROP DATATYPE, as DROP DOMAIN is the syntax used in the ANSI/ISO SQL3
draft.
Examples
(back to top)
- Example 1 drop the Departments table from the database:
- Example 2 drop the emp_dept view from the database:
- Example 3 drop the myDAS main cache from the simplex or
multiplex node you are connected to:
Usage
(back to top)
DROP removes the definition of
the indicated database structure. If the structure is a dbspace, then all tables
with any data in that dbspace must be dropped or relocated prior to dropping the
dbspace; other structures are automatically relocated. If the structure is a table,
all data in the table is automatically deleted as part of the dropping process.
Also, all indexes and keys for the table are dropped by DROP TABLE.
Side Effects
- Automatic commit. Clears the Data window in dbisql. DROP
TABLE and DROP INDEX close
all cursors for the current connection.
- Local temporary tables are an exception; no commit is
performed when one is dropped.
Standards
(back to top)
- SQL—ISO/ANSI SQL compliant.
- SAP Database products—Supported by SAP ASE.
Permissions
(back to top)
DBSPACE clause –
Requires
the DROP ANY OBJECT system privilege and user must be the only connection to the
database.
DOMAIN clause – Requires one of:
- DROP DATATYPE system privilege.
- DROP ANY OBJECT system privilege.
- You own the object.
FUNCTION clause – Requires one of:
- DROP ANY PROCEDURE system privilege.
- DROP ANY OBJECT system privilege.
- You own the function.
INDEX clause – Requires one of:
- DROP ANY INDEX system privilege.
- DROP ANY OBJECT system privilege.
- REFERENCES privilege on the underlying table being
indexed.
- You own the underlying table being indexed.
DBA or users with the appropriate privilege can drop an index on tables that
are owned other users without using a fully-qualified name. All other users must
provide a fully-qualified index name to drop an index on a base table owned by the
DBA.
MATERIALIZED VIEW clause –
Requires one of:
- DROP ANY MATERIALIZED VIEW system privilege.
- DROP ANY OBJECT system privilege.
- You own the materialized view.
PROCEDURE clause – Requires one of:
- DROP ANY PROCEDURE system privilege.
- DROP ANY OBJECT system privilege.
- You own the procedure.
TABLES clause –
Requires one of:
- DROP ANY TABLE system privilege.
- DROP ANY OBJECT system privilege.
- You own the table.
Global temporary tables cannot be dropped unless all users that have referenced
the temporary table have disconnected.
VIEW clause – Requires one of:
- DROP ANY VIEW system privilege.
- DROP ANY OBJECT system privilege.
- You own the view.
All other clauses – Requires one of:
- DROP
ANY OBJECT system privilege.
- You own the object.