SQL Reference Guide > SQL Reference Guide > SQL Statements > DROP
Was this helpful?
DROP
Valid in: SQL, ESQL, OpenAPI, ODBC, JDBC, .NET
The DROP statement destroys one or more tables, indexes, or views.
Note:  This statement has additional considerations when used in a distributed environment. For more information, see the Ingres Star User Guide.
The DROP statement has the following format:
[EXEC SQL] DROP [object_type] [IF EXISTS] [schema.]object_name {, [schema.]object_name} [RESTRICT | CASCADE];
object_type
Specifies the type of object to drop, which can be one of the following keywords:
TABLE
VIEW
INDEX
The following object types can also be dropped. These DROP statements are described under separate entries in this chapter:
DBEVENT (see DROP DBEVENT)
GROUP (see DROP GROUP)
INTEGRITY (see DROP INTEGRITY)
LINK (Applies to Ingres Star only. See the Ingres Star User Guide.)
LOCATION (see DROP LOCATION)
PROCEDURE (see DROP PROCEDURE)
PROFILE (see DROP PROFILE)
ROLE (see DROP ROLE)
RULE (see DROP RULE)
SCHEMA (see DROP SCHEMA)
SECURITY_ALARM (see DROP SECURITY_ALARM)
SEQUENCE (see DROP SEQUENCE)
SYNONYM (see DROP SYNONYM)
USER (see DROP USER)
IF EXISTS
Suppresses error reporting for the specified object if the object does not exist and the user matches the schema. The IF EXISTS option can be used only for object types TABLE, VIEW, INDEX, DBEVENT, RULE, SEQUENCE, SYNONYM, PROCEDURE, LINK.
object_name
Specifies the name of a table, view, index, or other valid object.
RESTRICT
Aborts the DROP statement with error if any objects (views, procedures, synonyms, or referential constraints) depend on the object being dropped.
This is the default for INDEX.
CASCADE
Drops all dependent objects. This is the default for TABLE and VIEW.
The DROP statement removes the specified tables, indexes, or views from the database. Any synonyms and comments defined for the specified object are also dropped. If the object is a table, any indexes, views, privileges, and integrities defined on that table are automatically dropped.
If an object type is not specified, Ingres assumes you are dropping a table, view, or index.
If the IF EXISTS clause is used and the specified object exists, Ingres drops it; if the specified object does not exist or exists but is not owned by you, Ingres does not return an error message. The IF EXISTS clause is typically used prior to issuing the corresponding CREATE statement to ensure that the CREATE statement does not fail due to an existing object of that type.
If the keyword indicating the object type is specified, the DBMS Server checks to make sure that the object named is the specified type. If more than one object is listed, only objects of the specified type are dropped. For example, if employee is a base table and emp_sal is a view on the base table salary, the following statement:
DROP TABLE employee, emp_sal;
drops only the employee base table (because the keyword TABLE was specified and emp_sal is a view, not a base table).
To drop a combination of table, views, and indexes in a single statement, omit the objecttype keyword. For example:
DROP employee, emp_sal;
If an object that is used in the definition of a database procedure is dropped, all permits on the procedure are dropped (the procedure is not dropped). The procedure cannot be executed, nor can the execute privilege be granted on the procedure until all the objects required by its definition exist.
All temporary tables are deleted automatically at the end of the session.
Embedded Usage
You cannot use host language variables in an embedded DROP statement. However, the DROP statement can be used in an embedded EXECUTE IMMEDIATE statement.
Permissions
You must be the owner of a table, view, or index.
Locking
The DROP statement takes an exclusive lock on the specified table.
Related Statements
CREATE TABLE
DECLARE GLOBAL TEMPORARY TABLE
CREATE VIEW
Last modified date: 08/28/2024