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.
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, ...) ;
- 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.