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.