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')), nt,
### 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. These language elements are documented in the Oracle SQL Language Reference, under the chapters labelled Pseudocolumns, Operators, Functions, Expressions, and Conditions:  
Another very good resource for all things SQL  
To test a particular formula, write it in the **SELECT** clause of a **SELECT** statement, e.g.

select 13 * 49, length('abracadabra'), add_months(Current_Date, 6) from Dual;
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.  

#### Implement and test the following integrity constraints:  
•	Site region should be Dunedin, Alexandra, or Tekapo.

•	Site altitude should be constrained according to New Zealand’s geography (from -2 m on the Taieri Plains to 3724 m for Aoraki/Mount Cook, but call it -10 m to 4000 m).  

•	The institute was founded in 2015, so there should be no samples before the start of that year.  

•	E-mail addresses should contain an ‘@’ symbol. (Extra challenge: ensure that there is only one ‘@’, and that it is not at the start or end.) 

•	Extra challenge: the first two characters of the site code should match the region (e.g. ‘DN’ for Dunedin).  

•	Extra challenge: refactor the region constraint to use a separate lookup table. What advantages would this bring?