Cascade Dropping Tables in PostgreSQL

Dropping tables with dependent objects is usually a tricky task. If the database is normalized, usually foreign keys will prevent the drop from occurring. Other objects such as views can also prevent dropping the object. It’s a very good thing these drops fail. We don’t want to accidentally blow away those necessary objects. However, if we’re testing scripts… maybe we do.

Cascade?

Looking at the DROP TABLEsyntax, you may notice a “CASCADE” keyword. This word should be familiar to you if you’ve played around with foreign key options enough.

Here’s the description from the PostgreSQL 8.1 docs:

DROP TABLE always removes any indexes, rules, triggers, and constraints that exist for the target table. However, to drop a table that is referenced by a view or a foreign-key constraint of another table, CASCADE must be specified. (CASCADE will remove a dependent view entirely, but in the foreign-key case it will only remove the foreign-key constraint, not the other table entirely.)

Demo

I’ll be using the sample dvdrental database for this demo. Let’s try executing the following statement:

DROP TABLE category;

That’s pretty straight forward. But look what happens.

ERROR: cannot drop table category because other objects depend on it
DETAIL: view actor_info depends on table category
view film_list depends on table category
view nicer_but_slower_film_list depends on table category
view sales_by_film_category depends on table category
constraint film_category_category_id_fkey on table film_category depends on table category
HINT: Use DROP ... CASCADE to drop the dependent objects too. SQL state: 2BP01

Wow, that hint is pretty helpful. Let’s modify the statement.

DROP TABLE category CASCADE;

Now we see the following messages:

NOTICE: drop cascades to 5 other objects
DETAIL: drop cascades to view actor_info drop cascades to view film_list
drop cascades to view nicer_but_slower_film_list
drop cascades to view sales_by_film_category
drop cascades to constraint film_category_category_id_fkey on table film_category
DROP TABLE

Query returned successfully in 57 msec.

Sure enough, I lost four views and one table.

Brief conclusion.

I really like this feature. It comes in handy for so many various reasons. It’s always frustrating trying to find each dependent object and dropping them one at a time. Along with the “DIE” method mentioned in a previous post, this can really make your scripts idempotent.

Leave a Reply

Your email address will not be published. Required fields are marked *