DROP TABLE

Syntax

DROP TABLE name [CASCADE | RESTRICT | CASCADE CONSTRAINTS]

Description

You can use the DROP TABLE command to delete tables from the database. Only the owner of a table can delete a table. To clear a table of rows without deleting the table, you can use the DELETE command. DROP TABLE always deletes indexes, rules, triggers, and constraints that exist for the target table.

Parameters

You can include the RESTRICT keyword to specify that the server does not drop the table if other objects depend on it. If objects depend on the table, the DROP TABLE command reports an error. This is the default behavior.

You can include the CASCADE clause to drop the objects that depend on the table.

You can include the CASCADE CONSTRAINTS clause to specify that the PolarDB database compatible with Oracle drops the dependent constraints (excluding other object types) on the specified table.

Examples

Drop a table named emp that has no dependencies:

DROP TABLE emp;

The results of a DROP TABLE command varies depending on whether the table has dependencies. Therefore, you can control the result by specifying a drop behavior. For example, you create two tables named orders and items, and 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, …) ;

Depending on the drop behavior that you specify, the PolarDB database compatible with Oracle drops the orders table as follows:

• If you specify DROP TABLE orders RESTRICT, the PolarDB database compatible with Oracle reports an error.
• If you specify DROPTABLE orders CASCADE, the PolarDB database compatible with Oracle drops the orders table and the items table.
• If you specify DROPTABLE orders CASCADE CONSTRAINTS, the PolarDB database compatible with Oracle drops the orders table and deletes the foreign key specification from the items table, but does not drop the items table.