This section is from the "Practical PostgreSQL" book, by John Worsley and Joshua Drake. Also available from Amazon: Practical PostgreSQL.
Unlike column constraints, a table constraint can be defined on one or more columns of a table. There are five variations of a table constraint. They are:
PRIMARY KEY
UNIQUE
CHECK
FOREIGN KEY
REFERENCES
The syntax to create a table constraint is:
[ CONSTRAINT name ] { PRIMARY KEY | UNIQUE } ( column [, ...] ) [ CONSTRAINT name ] CHECK ( condition ) [ CONSTRAINT name ] FOREIGN KEY ( column [, ...] ) REFERENCES reftable ( refcolumn [, ...] ) [ MATCH matchtype ] [ ON DELETE action ] [ ON UPDATE action ] [ [ NOT ] DEFERRABLE ] [ INITIALLY checktime ]
The PRIMARY KEY table constraint is similar to the PRIMARY KEY column constraint. The only difference is that the table constraint allows multiple columns to be defined. This column may contain only unique and non-null values.
This specifies that there cannot be any repeating values in the column(s).
This condition must be a boolean expression. It can use more than one column in this expression.
This means that a group of one or more distinct columns of a table is related to a group of distinct columns in the referenced table. Foreign keys are similar to column constraints, except for that it has the ability to include multiple columns.
Refers or points to another table column(s). More than one table can be used as a reference. The possible actions that the reference is associated with are the same as with a column constraint.
This creates a table constraint and a reference to the isbn column in the book table.
Example 5-23. Creating a Table Constraint
CREATE TABLE publish ( isbn INTEGER CONSTRAINT exist CHECK (isbn > 99999 and publ_name <> '') REFERENCES book (isbn) ON DELETE SET DEFAULT, publ_name TEXT, sugg_price MONEY );
 
Continue to: