Practical PostgreSQL

Advanced Table Techniques

PostgreSQL provides several ways to constrain values inserted and updated within tables. One of these is the availability of table and column constraints.

PostgreSQL also supports an advanced object-relational table concept called inheritance. Inheritance allows separate tables to have an explicit parent-child relationship and, through this relationship, share columns in a hierarchical fashion.

The following sections document both types of SQL constraints, as well as the creation and application of inherited tables.

Using Constraints

Constraints are table attributes used to define rules on the type of data values allowed within specified columns. By enforcing these rules within the database, you can effectively safeguard against incorrect or inappropriate data being added to the database.

When you create a table, you can create a constraint using the CREATE TABLE command’s CONSTRAINT clause. There are two types of constraints: column constraints and table constraints.

Column constraints apply only to a single column, while table constraints may apply to one or more columns. Within the CREATE TABLE command, the syntax for a column constraint follows immediately after a column definition, whereas the syntax for a table constraint exists in its own block, separated by a comma from any existing column definitions. A table constraint relies on its definition, rather than placement in the syntax, to indicate the columns affected by its restrictions.

The following ...