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:

  1. PRIMARY KEY

  2. UNIQUE

  3. CHECK

  4. FOREIGN KEY

  5. 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

 ]

[CONSTRAINT name ] PRIMARY KEY column [,...]

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.

[CONSTRAINT name ] UNIQUE column [,...]

This specifies that there cannot be any repeating values in the column(s).

CHECK condition

This condition must be a boolean expression. It can use more than one column in this expression.

FOREIGN KEY

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.

REFERENCES

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 
      );