![DISCONNECT statement [DBISQL]](arrow-left.gif){kind=icon}
Removes objects from the database.
DROP
{ DBSPACE dbspace-name
| { DATATYPE | DOMAIN } datatype-name
| EVENT event-name
| INDEX [ [owner].table-name.]index-name
| JOIN INDEX [ owner.]join-index-name
| MESSAGE message-number
| TABLE [ owner.]table-name
| VIEW [ owner.]view-name
| PROCEDURE [ owner.]procedure-name
| FUNCTION [ owner.]function-name }
Drops the department table from the database:
DROP TABLE department
Drops the emp_dept view from the database:
DROP VIEW emp_dept
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. However, you cannot drop the table if any join indexes use that table. You must first use DROP JOIN INDEX to remove the join indexes.
In a Sybase IQ multiplex, always perform DROP
JOIN INDEX operations in single-node mode on the write
server, then synchronize query servers. DROP JOIN INDEX returns
an error instead of propagating from write server to query server.
DROP INDEX deletes any explicitly created index. It only deletes an implicitly created index if there is no associated primary key, unique, or foreign-key constraints.
DROP INDEX for a nonunique HG index fails if an associated unenforced foreign key exists.
WARNING! Do not delete views owned by the DBO user. Deleting such views or changing them into tables might cause problems.
DROP TABLE, DROP INDEX, DROP JOIN INDEX, and DROP DBSPACE are prevented whenever the statement affects a table that is currently being used by another connection.
DROP TABLE is prevented if the primary table has foreign-key constraints associated with it, including unenforced foreign-key constraints
DROP TABLE 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 it to ' ' (an empty string), or set it 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. If a DBA is dropping a join index belonging to another user, the join index name must be qualified with an owner name.
The four initial dbspaces are SYSTEM, IQ_SYSTEM_MAIN, IQ_SYSTEM_TEMP, and IQ_SYSTEM_MSG. Any dbspace, except SYSTEM and IQ_SYSTEM_MSG, can be dropped using DROP DBSPACE, as long as there is at least one remaining dbspace with readwrite mode. You must relocate or 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. Dbspace names are case sensitive for databases created with CASE RESPECT.
A dbspace may contain data at any point after it is
used by a command, thereby preventing a DROP DBSPACE on
it.
See the section “Working with dbspaces” in Chapter 5, “Working with Database Objects” in the Sybase IQ System Administration Guide for more information on modifying dbspaces.
DROP PROCEDURE is prevented when the procedure is in use by another connection.
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.
Do
not use DROP DOMAIN on a multiplex query server
without a local IQ Main Store. Synchronizing the multiplex removes
domains from query servers without local stores. If the Query Server
has a local store, then both CREATE DOMAIN and DROP
DOMAIN are permitted.
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.
For DROP DBSPACE, must have DBA authority and must be the only connection to the database.
For others, must be the owner of the object, or have DBA authority.
Global temporary tables cannot be dropped unless all users that have referenced the temporary table have disconnected.
CREATE MESSAGE statement [T-SQL]
“sp_iqdbspace procedure” in Chapter 10, “System Procedures”
Chapter 5, “Working with Database Objects” in the Sybase IQ System Administration Guide.
