Newer
Older
labs / tiddlers / content / labs / lab03 / _Labs_03_CHECK Constraints.md

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:
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.

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?