The DROP TABLE command deletes an entire table and the data that is stored in the table.
Syntax
DROP TABLE name [CASCADE | RESTRICT | CASCADE CONSTRAINTS]
Description
The DROP TABLE
command can delete a table from a database. When you delete a table, all the partitions or subpartitions of the table are also deleted. To clear rows in a table without deleting the table, you can run the DELETE
command. DROPTABLE
always deletes indexes, rules, triggers, and constraints that exist for the required table.
To run the DROP TABLE command, 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
Parameter | Description |
---|---|
name | The name of a table or the partitioned table to be deleted. The name can be schema-qualified. |
You can include the RESTRICT
keyword to specify that the server does not delete the table if an object depends on the table. This is the default behavior. If an object depends on the table, the DROP TABLE
command reports an error.
You can include the CASCADE
clause to delete all the objects that depend on the table.
You can include the CASCADE CONSTRAINTS
clause to specify that PolarDB for PostgreSQL(Compatible with Oracle) deletes all the dependent constraints (excluding other object types) on the specified table.
Examples
Run the following command to delete the emp table that has no dependencies:
DROP TABLE emp;
The results of a DROP TABLE
command vary depending on whether the table has dependencies. Therefore, you can control the result by specifying the deletion behavior. For example, if you create the two tables 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, …) ;
PolarDB for PostgreSQL(Compatible with Oracle) performs one of the following operations when PolarDB for PostgreSQL(Compatible with Oracle) deletes the orders table. This depends on the deletion behavior that you specify.
- If you specify
DROPTABLE orders RESTRICT
, PolarDB for PostgreSQL(Compatible with Oracle) reports an error. - If you specify
DROPTABLE orders CASCADE
, PolarDB for PostgreSQL(Compatible with Oracle) deletes the orders table and the items table. - If you specify
DROPTABLE orders CASCADE CONSTRAINTS
, PolarDB for PostgreSQL(Compatible with Oracle) deletes the orders table and deletes the foreign key specification from the items table but does not delete the items table.