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): ```plaintext 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: https://www.h2database.com/html/grammar.html Another very good resource for all things SQL https://www.w3schools.com/sql/sql_intro.asp To test a particular formula, write it in the **SELECT** clause of a **SELECT** statement, e.g. ```plaintext 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?