CHECK

 The CHECK constraint enforces column value restrictions. Such constraints can restrict a column, for example, to a set of values, only positive numbers, or reasonable dates. Figure  shows an example of CHECK constraints using a modified version of the friend table from Figure , page .  

        test=> CREATE TABLE friend2 (
        test(>              firstname CHAR(15),
        test(>              lastname  CHAR(20),
        test(>              city      CHAR(15),
        test(>              state     CHAR(2)      CHECK (length(trim(state)) = 2),
        test(>              age       INTEGER      CHECK (age >= 0),
        test(>              gender    CHAR(1)      CHECK (gender IN ('M','F')),
        test(>              last_met  DATE         CHECK (last_met BETWEEN '1950-01-01'
        test(>                                            AND CURRENT_DATE),
        test(>              CHECK (upper(trim(firstname)) != 'ED' OR
        test(>                     upper(trim(lastname)) != 'RIVERS')
        test(> );
        CREATE
        test=> INSERT INTO friend2 
        test-> VALUES ('Ed', 'Rivers', 'Wibbleville', 'J', -35, 'S', '1931-09-23');
        ERROR:  ExecAppend: rejected due to CHECK constraint friend2_last_met  

This figure has many CHECK clauses:

state

Forces the column to be two characters long. CHAR()  pads the field with spaces, so state must be trim() -ed of trailing spaces before length()  is computed.

age

Forces the column to hold only positive values.

gender

Forces the column to hold either M or F.

last_met

Forces the column to include dates between January 1, 1950, and the current date.

table

Forces the table to accept only rows where firstname is not ED or lastname is not RIVERS. The effect is to prevent Ed Rivers from being entered into the table. His name will be rejected if it is in uppercase, lowercase, or mixed case. This restriction must be implemented as a table-level CHECK constraint. Comparing firstname to ED at the column level would have prevented all EDs from being entered, which was not desired. Instead, the desired restriction is a combination of firstname and lastname.

Next, the example tries to INSERT a row that violates all CHECK constraints. Although the CHECK failed on the friend2_last_met constraint, if that were corrected, the other constraints would prevent the insertion. By default, CHECK allows NULL  values.