CS145 Lecture Notes (8) -- Constraints and Triggers

We will cover constraints and triggers for relational databases only.
There is no accepted standard for constraints or triggers in XML data.
The non-demo portion of this material covers the SQL standard; actual systems tend to differ significantly.

Integrity Constraints

Integrity constraints impose restrictions on the allowable data in the database, in addition to the simple structure and type restrictions imposed by the basic schema definition.

Question: We've already seen a few kinds of integrity constraints. What are they?

Why use integrity constraints?

To catch data-entry errors
As correctness criteria when writing database updates
To enforce consistency across data in the database
To tell the system about the data - it may choose to store the data or process queries accordingly

-> Many useful constraints cannot be expressed with the kinds of constraints we've discussed so far.

Example schema:

   Student(ID, name, address, GPA, sizeHS)
   Campus(location, enrollment, rank)
   Apply(ID, location, date, major, decision)

Example constraints:

A student with GPA < 3.0 can only apply to campuses with rank > 4
All applications with date < 1/1/09 have non-NULL decision
Apply.ID and Apply.location appear in Student.ID and Campus.location, respectively
Campus.rank <= 10

Types of Integrity Constraints for Relational Databases

Non-null
Key
Referential integrity
Attribute-based
Tuple-based
General assertions

Declaring and Enforcing Constraints

Two times at which constraints may be declared:

Declared with original schema. Constraints must hold after bulk loading.
Declared later. Constraints must hold on current database.

After declaration, if a SQL statement causes a constraint to become violated then (in most cases) any effects of the statement are undone and a run-time error is generated.

[[On-Line Constraints Demo]]

Schema for examples:

   Student(ID, name, GPA, sizeHS)
   Campus(location, enrollment, rank)
   Apply(ID, location, major, decision)

Covered:

Non-null constraints
Key constraints
Referential integrity

Attribute-Based Constraints

(Note: MySQL syntactically accepts attribute-based and tuple-based constraints but does not enforce them.)

Constraints on values for Relation.Attribute

Type specifications are a kind of attribute-based constraint.
Non-null constraints are a kind of attribute-based constraint.

SQL Syntax: Add "CHECK(<condition>)" to attribute.
<condition> is like a SQL Where clause, can refer to attribute itself.
Examples:

   CREATE TABLE Student (...
                         GPA float CHECK(GPA <= 4.0 AND GPA > 0),
                         ...)

   CREATE TABLE Apply (...
                       decision char CHECK(decision in ('Y','N','U'))
                       ...)

Conditions can be more complex, e.g., with subqueries.
Example:

   Apply (...
          major char(10) CHECK(major NOT IN
                                  (SELECT dept-name FROM Dept
                                   WHERE status = 'full'))
          ...)

Question: Can we encode referential integrity constraints this way?

-> Attribute-based constraints are only checked on inserts and updates to that relation, NOT on modifications to relations referenced in subqueries -- significant "hole" in constraint-checking.

Tuple-Based Constraints

Constraints on values for Relation.Tuple

Similar to attribute-based constraints except CHECK applies to entire tuples.
=> Specified separately in table definition
Example:

  CREATE TABLE Campus(location char(25),
                      enrollment integer,
                      rank integer,
                      CHECK(enrollment >= 10,000 OR rank > 5)

(Example encodes enrollment < 10,000 => rank > 5)

As with attribute-based constraints, can have subqueries but constraint is only checked when the relation itself is modified

General Assertions

Constraints on entire relation or entire database

In SQL, stand-alone statement:

  CREATE ASSERTION <name> CHECK(<condition>)

Example: Average GPA is > 3.0 and average sizeHS is < 1000

  CREATE ASSERTION Avgs CHECK(
    3.0 < (SELECT avg(GPA) FROM Student) AND
    1000 > (SELECT avg(sizeHS) FROM Student))

Example: A student with GPA < 3.0 can only apply to campuses with rank > 4.

  CREATE ASSERTION RestrictApps CHECK(
     NOT EXISTS (SELECT * FROM Student, Apply, Campus
                 WHERE Student.ID = Apply.ID
                 AND Apply.location = Campus.location
                 AND Student.GPA < 3.0 AND Campus.rank <= 4))

Assertions checked for each modification that could potentially violate them.

Question: What operations could violate assertion Avgs?

Question: What operations could violate assertion RestrictApps?

Question: Can we encode referential integrity constraints this way?

Triggers

More general than constraints
In SQL-99 (SQL3) but not in SQL2, in most products in some form

Basic idea: When certain database modifications occur, automatically execute additional ones.

General form (SQL3):

  CREATE TRIGGER <name>
  BEFORE | AFTER | INSTEAD OF <events>
  <referencing clause>                // optional
  FOR EACH ROW                        // optional 
  WHEN (<condition>)                  // optional
  <action>

<events> can be:

  INSERT ON R
  DELETE ON R
  UPDATE [OF A1, A2, ..., An] ON R

AFTER <events> are the most common and useful.
<condition>: like general assertion
<action>: sequence of SQL statements
FOR EACH ROW
- If present, execute trigger once for each tuple changed.
- If absent, execute trigger once for each relevant statement.
- Terminology: "row-level" versus "statement-level"
- In either case, trigger executes after statement completes.
<referencing clause>:
REFERENCING <thing1> AS <var1> <thing2> AS <var2>, etc.
<thing> can be:
- OLD TABLE - previous values of deleted or updated tuples, row-level or statement-level, DELETE or UPDATE
- NEW TABLE - current values of inserted or updated tuples, row-level or statement-level, INSERT or UPDATE
- OLD ROW - previous value of deleted or updated tuple, row-level only, DELETE or UPDATE
- NEW ROW - current value of inserted or updated tuple, row-level only, INSERT or UPDATE

Examples

If an application tuple is inserted for a student with GPA > 3.9 and sizeHS > 1500 to Berkeley, set decision to "Y".

  CREATE TRIGGER AutoAccept
  AFTER INSERT ON Apply
  REFERENCING NEW ROW AS NewApp
  FOR EACH ROW
  WHEN (NewApp.location = 'Berkeley' AND
        3.9 < (SELECT GPA FROM Student WHERE ID = NewApp.ID) AND
        1500 < (SELECT sizeHS FROM Student WHERE ID = NewApp.ID))
  UPDATE Apply
    SET decision = 'Y'
    WHERE ID = NewApp.ID
    AND location = NewApp.location
    AND date = NewApp.date

Same trigger but no WHEN condition

  CREATE TRIGGER AutoAccept
  AFTER INSERT ON Apply
  REFERENCING NEW ROW AS NewApp
  FOR EACH ROW
  UPDATE Apply
    SET decision = 'Y'
    WHERE ID = NewApp.ID
    AND location = NewApp.location
    AND date = NewApp.date
    AND location = 'Berkeley'
    AND 3.9 < (SELECT GPA FROM Student WHERE ID = Apply.ID)
    AND 1500 < (SELECT sizeHS FROM Student WHERE ID = Apply.ID)

Same trigger but statement-level

  CREATE TRIGGER AutoAccept
  AFTER INSERT ON Apply
  REFERENCING NEW TABLE AS NewApps
  UPDATE Apply
    SET decision = 'Y'
    WHERE (ID,location,date) IN (SELECT ID,location,date FROM NewApps)
    AND location = 'Berkeley'
    AND 3.9 < (SELECT GPA FROM Student WHERE ID = Apply.ID)
    AND 1500 < (SELECT sizeHS FROM Student WHERE ID = Apply.ID)

If campus enrollment increases from below 7,000 to above 7,000, delete all applications to that campus dated after 2/15/08 and set all "Y" decisions for applications before 2/15/08 to "U".

  CREATE TRIGGER TooMany
  AFTER UPDATE OF enrollment ON Campus
  REFERENCING OLD ROW AS OldVal NEW ROW AS NewVal
  FOR EACH ROW
  WHEN (OldVal.enrollment <= 7,000 AND NewVal.enrollment > 7,000)
  DELETE FROM Apply
    WHERE location = NewVal.location AND date > 2/15/08;
  UPDATE Apply
    SET decision = 'U'
    WHERE location = NewVal.location
    AND decision = 'Y'

Triggers and assertions

Triggers are more flexible and expressive than assertions.
In practice the most common use of triggers is to effectively implement assertions.
Can check assertions and raise errors; can also automatically repair violations.

MySQL Restrictions on Trigger Specifications

No INSTEAD OF
No attributes for UPDATE
No statement-level; no OLD TABLE and NEW TABLE
No WHEN condition

Question: How much loss of expressiveness?

MySQL Restrictions on Trigger Interactions

Only one trigger per event type (detected at definition time)
Trigger cannot modify any table on which a trigger already ran (detected at runtime)
Very conservative restrictions

Question: What is being prevented by restricting triggering interactions?

[[On-Line Triggers Demo]]

Schema for examples:

   Student(ID, name, GPA, sizeHS)
   Campus(location, enrollment, rank)
   Apply(ID, location, major, decision)

Concepts covered:

Time: BEFORE and AFTER
Event: INSERT, DELETE, and UPDATE
References: NEW and OLD
Restrictions on single and multiple triggers
Triggers enforcing constraints