|The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language Reference
by The PostgreSQL Global Development Group
Paperback (6"x9"), 454 pages
RRP £14.95 ($19.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
Note that not all SQL commands are able to work on
inheritance hierarchies. Commands that are used for data querying,
data modification, or schema modification
most variants of
ALTER TABLE, but
ALTER TABLE ...
RENAME) typically default to including child tables and
ONLY notation to exclude them.
Commands that do database maintenance and tuning
typically only work on individual, physical tables and do no
support recursing over inheritance hierarchies. The respective
behavior of each individual command is documented in the reference
part ( Volume 1B: 1 SQL Commands).
A serious limitation of the inheritance feature is that indexes (including unique constraints) and foreign key constraints only apply to single tables, not to their inheritance children. This is true on both the referencing and referenced sides of a foreign key constraint. Thus, in the terms of the above example:
If we declared
PRIMARY KEY, this would not stop the
capitalstable from having rows with names duplicating rows in
cities. And those duplicate rows would by default show up in queries from
cities. In fact, by default
capitalswould have no unique constraint at all, and so could contain multiple rows with the same name. You could add a unique constraint to
capitals, but this would not prevent duplication compared to
Similarly, if we were to specify that
REFERENCESsome other table, this constraint would not automatically propagate to
capitals. In this case you could work around it by manually adding the same
Specifying that another table's column
REFERENCES cities(name)would allow the other table to contain city names, but not capital names. There is no good workaround for this case.
These deficiencies will probably be fixed in some future release, but in the meantime considerable care is needed in deciding whether inheritance is useful for your application.
Deprecated: In releases of PostgreSQL prior to 7.1, the default behavior was not to include child tables in queries. This was found to be error prone and also in violation of the SQL standard. You can get the pre-7.1 behavior by turning off the
|ISBN 9781906966041||The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language Reference||See the print edition|