5.12. Dependency Tracking

When you create complex database structures involving many tables with foreign key constraints, views, triggers, functions, etc. you implicitly create a net of dependencies between the objects. For instance, a table with a foreign key constraint depends on the table it references.

To ensure the integrity of the entire database structure,PostgreSQL makes sure that you cannot drop objects that other objects still depend on. For example, attempting to drop the products table we had considered inSection 5.3.5, with the orders table depending on it, would result in an error message such as this:

DROP TABLE products;NOTICE:  constraint orders_product_no_fkey on table orders depends on table productsERROR:  cannot drop table products because other objects depend on itHINT:  Use DROP ... CASCADE to drop the dependent objects too.

The error message contains a useful hint: if you do not want to bother deleting all the dependent objects individually, you can run:

DROP TABLE products CASCADE;

and all the dependent objects will be removed. In this case, it doesn't remove the orders table, it only removes the foreign key constraint. (If you want to check whatDROP ... CASCADE will do, runDROP withoutCASCADE and read theNOTICE messages.)

All drop commands inPostgreSQL support specifyingCASCADE. Of course, the nature of the possible dependencies varies with the type of the object. You can also writeRESTRICT instead ofCASCADE to get the default behavior, which is to prevent the dropping of objects that other objects depend on.