This topic describes how to delete a table.

Syntax

DROP TABLE name [CASCADE | RESTRICT | CASCADE CONSTRAINTS]

Parameters

Parameter Description
name The name of the table to drop. The name can be schema-qualified.

Description

DROP TABLE removes tables from the database. Only the owner of the table can destroy a table.

The DROP TABLE statement always removes any indexes, rules, triggers, and constraints that exist for the target table.

Note

Include the RESTRICT keyword to specify that the server must refuse to drop the table if any objects depend on it. This is the default behavior. The DROP TABLE statement displays an error if any objects depend on the table.

Include the CASCADE clause to drop any objects that depend on the table.

Include the CASCADE CONSTRAINTS clause to specify that PolarDB for Oracle must drop any dependent constraints that exclude other object types on the specified table.

Examples

The following statement drops a table named emp that has no dependencies:

DROP TABLE emp;

The outcome of a DROP TABLE statement varies depending on whether the table has any dependencies. You can control the outcome by specifying a drop behavior. For example, if you create two tables named orders and items, where the items table is dependent on the orders table: 

CREATE TABLE orders
  (order_id int PRIMARY KEY, order_date date, ...) ;
CREATE TABLE items
  (order_id REFERENCES orders, quantity int, ...) ;
PolarDB for Oracle performs one of the following actions when dropping the orders table, depending on the drop behavior that you specify:
  • If you specify DROP TABLE orders RESTRICT, PolarDB for Oracle will report an error.
  • If you specify DROP TABLE orders CASCADE, PolarDB for Oracle will drop the orders table and the items table.
  • If you specify DROP TABLE orders CASCADE CONSTRAINTS, PolarDB for Oracle will drop the orders table and remove the foreign key specification from the items table, but not drop the items table.