PolarDB:DROP TABLE

Syntax

DROP TABLE name [CASCADE | RESTRICT | CASCADE CONSTRAINTS]

Description

You can execute the DROP TABLE statement to delete a table from a database. When you delete a table, all partitions or subpartitions of the table are also deleted. To delete rows of a table without deleting the table, execute the DELETE statement. The DROP TABLE statement always deletes all indexes, rules, triggers, and constraints of a table.

To execute the DROP TABLE statement, you must be the owner of the partitioning root, a member of a group that owns the table, the schema owner, or a database superuser.

Parameters

The RESTRICT keyword specifies that PolarDB for PostgreSQL (Compatible with Oracle) does not delete the table that has dependent objects. This is the default behavior. If you execute the DROP TABLE statement to delete a table on which objects depend, an error is returned.

The CASCADE or CASCADE CONSTRAINTS clause specifies that PolarDB for PostgreSQL (Compatible with Oracle) deletes all dependent constraints but not other types of dependent objects for a table.

Examples

Delete a table named emp that has no dependent objects:

DROP TABLE emp;

The result of the DROP TABLE statement varies based on whether the table has dependent objects. You can specify a keyword or clause in the statement to control the result. For example, you create the orders and items tables and the items table depends on the orders table.

CREATE TABLE orders
  (order_id int PRIMARY KEY, order_date date, …)
CREATE TABLE items
  (order_id int REFERENCES orders, quantity int, …);

PolarDB for PostgreSQL (Compatible with Oracle) performs the following operations when it deletes the orders table based on the statement you execute:

• If you execute the DROP TABLE orders RESTRICT statement, PolarDB for PostgreSQL (Compatible with Oracle) returns an error.

• If you execute the DROP TABLE orders CASCADE CONSTRAINTS statement, PolarDB for PostgreSQL (Compatible with Oracle) deletes the orders table and deletes the foreign key constraint from the items table but does not delete the items table.