Advanced Table Techniques

Column constraints

Performing the \h CREATE TABLE slash command within psql displays several detailed syntax diagrams for the constraints that may be placed on a table. Here is the syntax for a column constraint:

  [ CONSTRAINT constraint_name ]
  { NOT NULL | UNIQUE | PRIMARY KEY | DEFAULT value | CHECK ( condition ) |
    REFERENCES table [ ( column ) ]
               [ MATCH FULL | MATCH PARTIAL ]
               [ ON DELETE action ]
               [ ON UPDATE action ]
               [ DEFERRABLE | NOT DEFERRABLE ]
               [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
  }

This syntax immediately follows the data type of the column to be constrained (and precedes any commas separating it from other columns) in the CREATE TABLE statement. It may be used with as many columns as is necessary. Notice that the CONSTRAINT keyword and constraint_name identifier are optional, and may be omitted.

There are six sets of column constraint keywords that may be applied. Some of the effects of these constraints are implicitly defined by others. The constraint keywords are as follows:

The sixth column constraint, REFERENCES, contains the following clauses:

Example 7-7 shows how to create a table called employees with a variety of simple constraints.

booktown=# CREATE TABLE employees
booktown-#             (id integer PRIMARY KEY CHECK (id > 100),
booktown(#              last_name text NOT NULL,
booktown(#              first_name text);
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'employees_pkey'
for table 'employees'
CREATE

Example 7-7 creates a column called id, of type integer which has both a PRIMARY KEY constraint and a CHECK constraint. The PRIMARY KEY constraint implies both NOT NULL and UNIQUE, as well as implicitly creates the employees_ pkey index to be used on the column. The CHECK constraint verifies that the value of id is greater than 100. This means that any attempt to INSERT or UPDATE row data for the employees table with an id value of less-than or equal-to 100 will fail.

The employees table created in Example 7-7 also contains a column named last_name of type text which has a NOT NULL constraint enforced. This is a much simpler constraint; it disallows the addition of employees whose last name values are input as NULL. In other words, users must supply a last name for each employee.

Table constraints

Unlike column constraints, a table constraint can be defined on more than one column of a table. Here is the syntax to create a table constraint:

  [ CONSTRAINT constraint_name ]
  { UNIQUE ( column [, ...] ) |
    PRIMARY KEY ( column [, ...] ) |
    CHECK ( condition ) |
    FOREIGN KEY ( column [, ... ] )
                REFERENCES table [ ( column [, ... ] ) ]
                [ MATCH FULL | MATCH PARTIAL ]
                [ ON DELETE action ]
                [ ON UPDATE action ]
                [ DEFERRABLE | NOT DEFERRABLE ]
                [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

CONSTRAINT constraint_name provides an optional name for the constraint. Naming a constraint is recommended, as it provides you with a meaningful name for the purpose of the constraint, rather than an automatically generated, generic name. In the future, this name also may be useful in removing constraints (e.g., when PostgreSQL's DROP CONSTRAINT clause of the ALTER TABLE implemented). The other clauses define four general types of table constraints:

Example 7-8 creates the Book Town editions table. It creates three table constraints. A detailed explanation follows the example.

booktown=# CREATE TABLE editions
booktown-#              (isbn text,
booktown(#              book_id integer,
booktown(#              edition integer,
booktown(#              publisher_id integer,
booktown(#              publication date,
booktown(#              type char,
booktown(#              CONSTRAINT pkey PRIMARY KEY (isbn),
booktown(#              CONSTRAINT integrity CHECK (book_id IS NOT NULL
booktown(#                                          AND edition IS NOT NULL),
booktown(#              CONSTRAINT book_exists FOREIGN KEY (book_id)
booktown(#                         REFERENCES books (id)
booktown(#                         ON DELETE CASCADE
booktown(#                         ON UPDATE CASCADE);
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'pkey' for table
'editions'
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE

The first constraint, pkey is a PRIMARY KEY constraint on the isbn column, and behaves identically to a PRIMARY KEY column constraint (because only one column target is supplied).

The constraint named integrity uses the CHECK clause to ensure that neither the book_id nor edition columns ever contain NULL values.

Finally, the book_exists constraint uses the FOREIGN KEY and REFERENCES clauses to verify that the book_id value always exists within the books table in the id column. Furthermore, since the CASCADE keyword is supplied for both the ON DELETE and ON UPDATE clauses, any modifications to the id column in the books table will also be made to the corresponding rows of the editions table, and any deletions from books will result in corresponding rows being deleted from editions.

Notice that both an implicit index named editions_pkey on the isbn column and an implicit trigger are created from these table constraints. The implicit index is used in the enforcement of the PRIMARY KEY constraint. The implicit trigger enforces the FOREIGN KEY constraint.

Adding a constraint

The ALTER TABLE command is intended to allow the addition of table constraints to existing tables. As of PostgreSQL 7.1.x, however, only the addition of CHECK and FOREIGN KEY constraints is supported.

Here is the syntax to add a constraint with ALTER TABLE:

  ALTER TABLE table
        ADD [ CONSTRAINT name ]
        { CHECK ( condition ) |
          FOREIGN KEY ( column [, ... ] )
                      REFERENCES table [ ( column [, ... ] ) ]
                      [ MATCH FULL | MATCH PARTIAL ]
                      [ ON DELETE action ]
                      [ ON UPDATE action ]
                      [ DEFERRABLE | NOT DEFERRABLE ]
                      [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
        }

Example 7-9 adds a FOREIGN KEY constraint to the Book Town books table's subject_id column. This constraint references the id column within the subjects table, and will ensure that no values are inserted or modified on the subject_id column with a value which cannot be found in the subjects table's id column.

booktown=# ALTER TABLE books ADD CONSTRAINT legal_subjects
booktown-#                   FOREIGN KEY (subject_id)
booktown-#                   REFERENCES subjects (id);
NOTICE:  ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for
FOREIGN KEY check(s)
CREATE

Removing a constraint

As of PostgreSQL 7.1.x, constraints may not be directly removed from a table. The only way to achieve the net effect of dropping a constraint is to create a copy of the table which is nearly identical to the original, with any unwanted constraints omitted. The data can then be copied from the original table to the new table, and the tables renamed using the ALTER TABLE command so that the new copy replaces the original table.

Example 7-10 demonstrates this work-around method for removing a constraint by effectively removing the legal_subjects FOREIGN KEY constraint from the books table (see Example 7-9). Notice that the books_id_pkey is removed before the new table is created, so that the new table can be created with an index named books_id_ pkey. This is not necessary, but for the sake of consistency we want to keep the primary key index name the same.

booktown=# DROP INDEX books_id_pkey;
DROP
booktown=# CREATE TABLE new_books
booktown-#              (id integer CONSTRAINT books_id_pkey PRIMARY KEY,
booktown(#              title text NOT NULL,
booktown(#              author_id integer,
booktown(#              subject_id integer);
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'books_id_pkey'
for table 'new_books'
CREATE
booktown=# INSERT INTO new_books SELECT * FROM books;
INSERT 0 15
booktown=# ALTER TABLE books RENAME TO old_books;
ALTER
booktown=# ALTER TABLE new_books RENAME TO books;
ALTER

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)