PostgreSQL table Dependency Tracking

If you create a complex database structure that contains many tables with foreign key constraints, views, triggers, functions, and so on. Then you implicitly create a dependent network between the objects. For example, a table with a FOREIGN key constraint depends on the table it refers to.

To ensure the integrity of the entire database structure,PostgreSQL guarantees that you cannot delete objects that are also dependent on other objects. For example, an attempt to delete a product table on which the order table depends is not successful, and there will be an error message similar to the following:

DROP TABLE Products; NOTICE:  constraint orders_product_no_fkey on table orders depends on table Productserror:  cannot drop table Produ CTS because other objects depend on Ithint: Use  DROP ... CASCADE to drop the dependent objects too.

This error message contains a helpful hint: if you don't want to bother to remove all dependent objects separately, you can run:

DROP TABLE Products CASCADE;

All dependent objects are then deleted (The order table is not deleted, but the foreign key constraint is removed). If you want to check the DROP ... What will CASCADE do, run the DROP without CASCADE and read the NOTICE message.

All Delete commands in PostgreSQL support the declaration of CASCADE. Of course, a specific dependent entity depends on the type of object. You can also write RESTRICT instead of CASCADE to get the default behavior (to prevent the deletion of objects that other objects depend on).

Note: according to the SQL standard, you are required to declare at least one of RESTRICT or CASCADE . There is actually no database system that enforces this, but the default behavior is RESTRICT or CASCADE , which varies by system.

Note: foreign KEY constraint dependencies and sequence field dependencies prior to PostgreSQL 7.3 will not be maintained or created during the upgrade process. All other dependency types will be properly created during the database upgrade process prior to version 7.3.

PostgreSQL table Dependency Tracking