DROP 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

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 O Edition 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-O performs one of the following operations when PolarDB O Edition deletes the orders table. This depends on the deletion behavior that you specify.

• If you specify DROPTABLE orders RESTRICT, PolarDB O Edition reports an error.
• If you specify DROPTABLE orders CASCADE, PolarDB O Edition deletes the orders table and the items table.
• If you specify DROPTABLE orders CASCADE CONSTRAINTS, PolarDB O Edition deletes the orders table and deletes the foreign key specification from the items table but does not delete the items table.