labs / tiddlers / content / labs / lab03 / _Labs_03_CHECK

Any additional validation rules for the database can be enforced using the general-purpose CHECK constraint type. The body of a CHECK constraint is a condition (true/false expression), which will usually centre around the following:

• Equality (=)
• Inequality (>, <, >=, <=, BETWEEN)
• Set membership (IN)
• The string pattern matching operator, LIKE (and NOT LIKE)
• The logical operators AND, OR, NOT

Here is a typical CHECK constraint (but see the lectures for further examples):

    … constraint Sale_Shipped check (Shipped in ('T', 'F')),

SQL’s Expression Language

Most SQL dialects have an extensive library of elements that you can use to build database-specific formulae. These can be used in most places a value is expected, including when defining integrity constraints. For example:

Also often used in queries in conjusnction wit the WHEWE clause but can be used independantly:

w3 is a very good resource for all things SQL (not database specific):

To test a particular formula, write it in the SELECT clause of a SELECT statement, e.g.

select 13 * 49, length('abracadabra'), Current_Date + 6 ;

When implementing any complex CHECK constraint, test your formula thoroughly first using a SELECT statement as shown, especially if you are using unfamiliar operators or functions. You will find this much quicker and easier than having to implement the entire table before testing.