In PostgreSQL, the CASCADE option is used with the DROP TABLE command to drop the tables that have dependent objects. In PostgreSQL, the DROP TABLE drops single or multiple tables. However, the DROP TABLE command can’t drop a table that contains dependent objects.

So, how to drop/delete a table that has dependent objects? Well! Thanks to the CASCADE option that allows us to drop a table along with its dependent objects.

This article will explain the Postgres DROP TABLE CASCADE statement with the help of examples. So, let’s get started!

What is the Need For the CASCADE Option in PostgreSQL?

In PostgreSQL, a database can have multiple tables, and each table can have a relation with other tables. So, any table that has some dependent objects can’t be deleted/dropped using the DROP TABLE statement. The below example will assist you in understanding the need for the CASCADE option.

Example: How to Drop a Table That has Dependent Objects Using DROP TABLE Statement?

We have already created “company_details” and “employee_info” tables in our database. Let’s execute the \d command to get all the details of the selected tables:

\d company_details;

From the above snippet, you can observe that the company_id column is referred as a foreign key in the employee_info table.

Let’s describe the “employee_info” table using the “\d” command:

\d employee_info;

The above snippet shows that the employee_info table depends on the company_details table.

Now, let’s try to drop the company_details table using the DROP TABLE:

DROP TABLE comapny_details;

When we executed the DROP TABLE command, we encountered an error that says can’t drop a table that has dependent objects.

How to Drop a Table That has Dependent Objects in PostgreSQL?

Use the CASCADE option with the DROP TABLE command to remove a table along with its dependent objects. The syntax of the DROP TABLE command with the CASCADE option will be as follows:

DROP TABLE tab_name CASCADE;

Here, tab_name represents a table that needs to be dropped.

Example: How Does the DROP TABLE CASCADE Work in PostgreSQL?

Let’s execute the DROP TABLE command with the aid of the CASCADE option to drop a table that has dependent objects:

DROP TABLE company_details CASCADE;

The output clarifies that the CASCADE option succeeded in dropping the table with its dependent objects. The notice depicts that the dependent objects have also been removed from the table that is being dropped.

Let’s verify the table deletion using the below-given command:

\d company_details;

The output shows that the company_details table has been successfully dropped from the database.

Let’s execute the below command to describe the employee_info table:

\d employee_info;

The output shows that the company_id is no more a foreign key.

Conclusion

In PostgreSQL, the CASCADE option is used with the DROP TABLE statement to drop/delete a table and its dependent objects. To do so, specify the DROP TABLE command followed by the table name and then write the CASCADE to drop a table along with its dependent objects. This write-up discussed the working of the DROP TABLE command with the CASCADE option using multiple examples.