Dependency Tracking

2.10. Dependency Tracking

When you create complex database structures involving many tables with foreign key constraints, views, triggers, functions, etc. you will 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 in Section 2.4.5, with the orders table depending on it, would result in an error message such as this:

DROP TABLE products;
NOTICE:  constraint $1 on table orders depends on table products
ERROR:  Cannot drop table products because other objects depend on it
        Use DROP ... CASCADE to drop the dependent objects too

The error message contains a useful hint: If you don't 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 what DROP ... CASCADE will do, run DROP without CASCADE and read the NOTICE messages.)

All drop commands in PostgreSQL support specifying CASCADE. Of course, the nature of the possible dependencies varies with the type of the object. You can also write RESTRICT instead of CASCADE to get the default behavior which is to restrict drops of objects that other objects depend on.

Note: According to the SQL standard, specifying either RESTRICT or CASCADE is required. No database system actually implements it that way, but whether the default behavior is RESTRICT or CASCADE varies across systems.

Note: Foreign key constraint dependencies and serial column dependencies from PostgreSQL versions prior to 7.3 are not maintained or created during the upgrade process. All other dependency types will be properly created during an upgrade.

Home
Online Resources
General
Beginner Tutorials
MySQL
PostgreSQL
Oracle
mSQL
Microsoft SQL
Contact Us
Random quote of the moment:
I really hate this damned machine I wish that they would sell it. It never does quite what I want But only what I tell it.
 
http://www.sql.org/
 
Proudly designed and hosted by OmegaSphere, providers of all your Web Hosting, SSL Certificate, and Domain Name needs!