Creating a child table

A child table is created with the CREATE TABLE SQL command by using the INHERITS clause. This clause consists of the INHERITS keyword, and the name of the table (or tables) from which to inherit.

Here is the portion of the CREATE TABLE syntax which applies to inheritance:

  CREATE TABLE 
childtable
 
definition

         INHERITS ( 
parenttable
 [, ...] )

In this syntax, childtable is the name of the new table to be created, definition is the complete table definition (with all the ordinary CREATE TABLE clauses), and parenttable is the table whose column structure is to be inherited. More than one parent table may be specified by separating table names with commas.

Example 7-11 creates a table called distinguished_authors with a single column named award of type text. Since it is instructed to inherit from the authors table by the INHERITS clause it actually is created with four columns; the first three from authors, and the fourth awards column.

booktown=# 
CREATE TABLE distinguished_authors (award text)

booktown-# 
             INHERITS (authors);

CREATE
booktown=# 
\d distinguished_authors

  Table "distinguished_authors"
 Attribute  |  Type   | Modifier
------------+---------+----------
 id         | integer | not null
 last_name  | text    |
 first_name | text    |
 award      | text    |

As you can see, even though Example 7-11 specified only one column, the distinguished_authors table inherited all of the columns that were originally in the authors table.

Using inherited tables

The relationship between the shared columns of a parent and child table is not purely cosmetic. Inserted values on the distinguished_authors table will also be visible in the authors table, its parent. However, in the authors table, you will only see the three columns which were inherited. When querying a parent table, you can use the ONLY keyword to specify that rows from child tables are to be omitted from the query results.

Example 7-12 inserts a new author named Neil Simon with the award of Pulitzer Prize into the distinguished_authors table. Notice that the first three inserted values are shared between the parent and child tables.

booktown=# 
INSERT INTO distinguished_authors

booktown-# 
            VALUES (nextval('author_ids'),

booktown(# 
            'Simon', 'Neil', 'Pulitzer Prize');

INSERT 3629421 1

Since the first three columns of the distinguished_authors table are inherited from the authors table, this author will also appear implicitly as a regular author in the authors table (though the data is not literally inserted into the authors table). Only the distinguished_authors table will show information about awards, however, as inheritance only works one way (descending from parent to child).

Example 7-13 executes three SELECT statements. Each of these queries chooses a different target for the FROM clause, while using the same search criteria in the WHERE clause.

booktown=# 
SELECT * FROM distinguished_authors

booktown-# 
         WHERE last_name = 'Simon';

  id   | last_name | first_name |     award
-------+-----------+------------+----------------
 25043 | Simon     | Neil       | Pulitzer Prize
(1 row)

booktown=# 
SELECT * FROM authors WHERE last_name = 'Simon';

  id   | last_name | first_name
-------+-----------+------------
 25043 | Simon     | Neil
(1 row)

booktown=# 
SELECT * FROM ONLY authors WHERE last_name = 'Simon';

 id | last_name | first_name
----+-----------+------------
(0 rows)

Each of the three queries in Example 7-13 look for rows where the last_name column matches the string constant Simon . The first query selects from the distinguished_authors table, which the data was originally inserted into (in Example 7-12), and the requested row is returned.

The second query in Example 7-13 selects from the parent of distinguished_authors, which is the authors table. Again, a row is retrieved, though this row includes only the columns which are inherited by the distinguished_authors.

It is important to understand that this data was not literally inserted into both tables, but simply made visible because of the inheritance relationship. This is illustrated by the third and final query in Example 7-13, which prefixes the authors table name with the ONLY keyword. This keyword indicates that rows are not to be received from child tables, but only from the specified parent; as a result, no rows are returned by the query.

Some constraints may appear to be violated because of the nature of inherited tables. For example, a column with a UNIQUE constraint placed on it may appear to have the same value twice by including data from inherited children. Make careful use of constraints and inheritance, as a child table does not literally violate such a constraint, though it can appear to if the ONLY keyword is not used when selecting from the parent table.

Modifying inherited tables

As covered in the preceding section, adding values into child and parent tables is fairly straightforward. An insertion of values into a child table will cause values in inherited columns to appear as values in the parent table, though the data itself physically resides in the child table. Insertion of values into a parent table has no effect whatsoever on the child table.

Likewise, modifying values in a child table is self-explanatory: only the values in the child table are modified, while any values literally in the parent table are unmodified. This is because the data is not literally shared between tables, but can only be viewed through the hierarchy. A retrieval of rows on the parent table without the ONLY clause will still show both the parent rows, and the modified child rows.

The effect of modifying existing rows in a parent table is less obvious than the effect of modifying existing rows in a child table. UPDATE and DELETE statements executed on a parent table will, by default, affect not only rows in the parent table, but also any child tables that match the criteria of the statement. Example 7-14 performs an UPDATE statement on the authors table. Notice that the row data in the distinguished_authors table is actually affected by this statement.

booktown=# 
UPDATE authors SET first_name = 'Paul'

booktown-# 
               WHERE last_name = 'Simon';

UPDATE 1
booktown=# 
SELECT * FROM distinguished_authors;

  id   | last_name | first_name |     award
-------+-----------+------------+----------------
 25043 | Simon     | Paul       | Pulitzer Prize
(1 row)

The ONLY keyword can be used with UPDATE and DELETE in a fashion similar to its use with the SELECT command in order to prevent the type of cascading modification illustrated in Example 7-14. The ONLY keyword should always precede the inherited table name in the SQL syntax.

Example 7-15 demonstrates the use of the ONLY keyword. First, the example inserts a new row for Dr. Seuss into the distinguished_authors table, along with a reference to his Pulitzer Prize. This results in the authors table appearing to have two separate entries for the same author. The old entry (that exists physically in the authors table) is then removed by use of the DELETE SQL command combined with the ONLY keyword.

booktown=# 
INSERT INTO distinguished_authors

booktown-# 
            VALUES (1809, 'Geisel', 

booktown(# 
            'Theodor Seuss', 'Pulitzer Prize');

INSERT 3629488 1
booktown=# 
SELECT * FROM authors

booktown-# 
         WHERE last_name = 'Geisel';

  id  | last_name |  first_name
------+-----------+---------------
 1809 | Geisel    | Theodor Seuss
 1809 | Geisel    | Theodor Seuss
(2 rows)

booktown=# 
DELETE FROM ONLY authors 

booktown-# 
       WHERE last_name = 'Geisel';

DELETE 1

The end result of Example 7-15 is that the record for Dr. Seuss is added to the distinguished_authors table, and subsequently removed from the authors table, as follows:

booktown=# 
SELECT * FROM authors 

booktown-# 
         WHERE last_name = 'Geisel';

  id  | last_name |  first_name
------+-----------+---------------
 1809 | Geisel    | Theodor Seuss
(1 row)

booktown=# 
SELECT * FROM distinguished_authors 

booktown-# 
         WHERE last_name = 'Geisel';

  id  | last_name |  first_name   |     award
------+-----------+---------------+----------------
 1809 | Geisel    | Theodor Seuss | Pulitzer Prize
(1 row)