$PostgreSQL: pgsql/doc/src/sgml/advanced.sgml,v 1.44 2004/08/08 01:51:05 momjian Exp $

city varchar(80) primary key,

location point

city varchar(80) references cities(city),

temp_lo int,

temp_hi int,

prcp real,

date date

Inheritance is a concept from object-oriented databases. Although

it opens up interesting new possibilities of database design,

this feature is currently unmaintained and known to have serious

gotchas in its foreign key implementation, which you should take

care to avoid. The fixes below are probably version-specific and may

require updates in the future.

<para>

The example below illustrates the gotcha.

</para>

<para>

<programlisting>

BEGIN;

CREATE TABLE foo (

foo_id SERIAL PRIMARY KEY

);

CREATE TABLE parent (

parent_id SERIAL PRIMARY KEY

, foo_id INTEGER NOT NULL REFERENCES foo(foo_id) ON DELETE CASCADE

, parent_1_text TEXT NOT NULL

);

CREATE TABLE child_1 (

child_1_text TEXT NOT NULL

) INHERITS(parent);

CREATE TABLE child_2 (

child_2_text TEXT NOT NULL

) INHERITS(parent);

INSERT INTO foo VALUES(DEFAULT);

INSERT INTO child_1 (foo_id, parent_1_text, child_1_text)

VALUES (currval('public.foo_foo_id_seq'), 'parent text 1', 'child_1 text 1');

INSERT INTO foo VALUES(DEFAULT);

INSERT INTO child_1 (foo_id, parent_1_text, child_1_text)

VALUES (currval('public.foo_foo_id_seq'), 'parent text 2', 'child_1 text 2');

INSERT INTO foo VALUES(DEFAULT);

INSERT INTO child_2 (foo_id, parent_1_text, child_2_text)

VALUES (currval('foo_foo_id_seq'), 'parent text 3', 'child_2 text 1');

DELETE FROM foo WHERE foo_id = 1;

SELECT * FROM parent;

parent_id | foo_id | parent_1_text

-----------+--------+---------------

1 | 1 | parent text 1

2 | 2 | parent text 2

3 | 3 | parent text 3

(3 rows)

SELECT * FROM child_1;

parent_id | foo_id | parent_1_text | child_1_text

-----------+--------+---------------+----------------

1 | 1 | parent text 1 | child_1 text 1

2 | 2 | parent text 2 | child_1 text 2

(2 rows)

ROLLBACK;

</programlisting>

</para>

Oops!! None of parent, child or foo should have any rows with

foo_id = 1 in them. Here is a way to fix the above tables.

</para>

<para>

To fix the gotcha, you must put foreign key constraints on each of

the child tables, as they will not be automatically inherited as

you might expect.

</para>

<para>

<programlisting>

ALTER TABLE child_1 ADD CONSTRAINT cascade_foo

FOREIGN KEY (foo_id) REFERENCES foo(foo_id) ON DELETE CASCADE;

ALTER TABLE child_2 ADD CONSTRAINT cascade_foo

FOREIGN KEY (foo_id) REFERENCES foo(foo_id) ON DELETE CASCADE;

</programlisting>

</para>

<para>

That caveat out of the way, let's create two tables: A table

<classname>cities</classname> and a table

<classname>capitals</classname>. Naturally, capitals are also cities,

so you want some way to show the capitals implicitly when you list all

cities. If you're really clever you might invent some scheme like

this:

<!-- $PostgreSQL: pgsql/doc/src/sgml/ddl.sgml,v 1.29 2004/08/07 20:44:49 tgl Exp $ -->

In this case, a row of capitals <firstterm>inherits</firstterm> all

attributes (name, population, and altitude) from its

parent, cities. The type of the attribute name is

<type>text</type>, a native <productname>PostgreSQL</productname> type

for variable length character strings. The type of the attribute

population is

<type>float</type>, a native <productname>PostgreSQL</productname> type for double precision

floating-point numbers. State capitals have an extra

attribute, state, that shows their state. In <productname>PostgreSQL</productname>,

a table can inherit from zero or more other tables,

and a query can reference either all rows of a

table or all rows of a table plus all of its

descendants.

<note>

<title>Deprecated</title>

<para>

In previous versions of <productname>PostgreSQL</productname>, the

default behavior was not to include child tables in queries. This was

found to be error prone and is also in violation of the SQL99

standard. Under the old syntax, to get the sub-tables you append

<literal>*</literal> to the table name.

For example

<programlisting>

SELECT * from cities*;

</programlisting>

You can still explicitly specify scanning child tables by appending

<literal>*</literal>, as well as explicitly specify not scanning child tables by

writing <quote>ONLY</quote>. But beginning in version 7.1, the default

behavior for an undecorated table name is to scan its child tables

too, whereas before the default was not to do so. To get the old

default behavior, set the configuration option

<literal>SQL_Inheritance</literal> to off, e.g.,

<programlisting>

SET SQL_Inheritance TO OFF;

</programlisting>

or add a line in your <filename>postgresql.conf</filename> file.

</para>

</note>

A limitation of the inheritance feature is that indexes (including

tables, not to their inheritance children. Thus, in the above example,

specifying that another table's column <literal>REFERENCES cities(name)</>

would allow the other table to contain city names but not capital names.

This deficiency will probably be fixed in some future release.

$PostgreSQL: pgsql/doc/src/sgml/query.sgml,v 1.36 2004/08/08 01:51:05 momjian Exp $

(here <literal>*</literal> means <quote>all columns</quote>.

Note: While <literal>SELECT *</literal> is useful for off-the-cuff

queries, it is considered bad style in production code for

maintenance reasons) and the output should be: