Drop

Valid in: SQL, ESQL

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.

Syntax

The DROP statement has the following format:

[EXEC SQL] DROP objecttype [schema.]objectname {, [schema.]objectname};

• objecttype

  Specifies the type of object, which can be one of the following keywords:

  • TABLE
  • VIEW
  • INDEX
• objectname

  Specifies the name of a table, view, or index.

Description

The DROP statement removes the specified tables, indexes, and views from the database. Any synonyms and comments defined for the specified table, view, or index are also dropped. If the object is a table, any indexes, views, privileges, and integrities defined on that table are automatically dropped.

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

Examples: Drop

The following are DROP statement examples:

1. Drop the employee and dept tables.

   drop table employee, dept;

2. Drop the salary table and its index, salidx, and the view, emp_sal.

   drop salary, salidx,
   accounting.emp_sal;

3. In an embedded program, drop two views.

   exec sql drop view tempview1, tempview2;