Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com
Answertopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

Inheritance

PostgreSQL supports an advanced object-relational mechanism known as inheritance . Inheritance allows a table to inherit some of its column attributes from one or more other tables, creating a parent-child relationship. This causes the child table to have each of the same columns and constraints as its inherited table (or tables), as well as its own defined columns.

When performing a query on an inherited table, the query can be instructed to retrieve either all rows of a table and its descendants, or just the rows in the parent table itself. The child table, on the other hand, will never return rows from its parent.

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.

Example 7-11. Creating a child table

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.

Note: Parent rows are never visible within a query on one of its child tables. Therefore, using the ONLY keyword on a child table would only have an effect if that child table were also inherited by another table, making it effectively both a parent and a child.

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.

Example 7-12. Inserting into a child table

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.

Example 7-13. Selecting with inheritance

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.

Example 7-14. Modifying parent and child tables

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.

Example 7-15. Modifying parent tables with ONLY

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)
Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire