|PostgreSQL Reference Manual - Volume 1 - SQL Language Reference|
by The PostgreSQL Global Development Group
Paperback (6"x9"), 716 pages
RRP £32.00 ($49.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
3.11 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 3.3.5 Foreign Keys, 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 products ERROR: cannot drop table products because other objects depend on it HINT: 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 what
DROP ... CASCADE will do,
CASCADE and read the
All drop commands in PostgreSQL support
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
prevent drops of objects that other objects depend on.
Note: According to the SQL standard, specifying either
CASCADEis required. No database system actually enforces that rule, but whether the default behavior is
CASCADEvaries 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 from a pre-7.3 database.
|ISBN 0954612027||PostgreSQL Reference Manual - Volume 1 - SQL Language Reference||See the print edition|