SQL: Constraints and Triggers

1
SQL Constraints and Triggers

• Chapter 5, 5.7-5.8

2
Integrity Constraints (Review)

• An IC describes conditions that every legal
  instance of a relation must satisfy.
• Inserts/deletes/updates that violate ICs are
  disallowed.
• Can be used to ensure application semantics
  (e.g., sid is a key), or prevent inconsistencies
  (e.g., sname has to be a string, age must be lt
  200)
• Types of ICs Domain constraints, primary key
  constraints, foreign key constraints, general
  constraints.
• Domain constraints Field values must be of
  right type. Always enforced.

3
General Constraints CHECK

• Syntax CHECK conditional-expression.
• The conditional expression captures more general
  ICs than keys.
• The conditional expressions can use queries.
• The conditional expressions required to hold only
  if the associated table is nonempty.
• A CHECK constraint may be expressed over several
  tables however, it is often expressed over one
  single table.
• Constraints can be named
• CONSTRAINT MyConstraint
• CHECK conditional-expression

4
CHECK Constraints Examples
Constraint Rating must be in the range 1 to
10 CREATE TABLE Sailors ( sid
INTEGER, sname CHAR(10), rating INTEGER, age
REAL, PRIMARY KEY (sid), CHECK ( rating gt 1
AND rating lt 10 ))
5
CHECK Constraints Examples
Constraint Interlake boats cannot be reserved
6
General Constraints ASSERTION
Constraint Number of boats plus number of
sailors is lt 100
CREATE TABLE Sailors ( sid INTEGER, sname
CHAR(10), rating INTEGER, age REAL, PRIMARY
KEY (sid), CHECK ( (SELECT COUNT (S.sid)
FROM Sailors S) (SELECT COUNT (B.bid) FROM
Boats B) lt 100 )

• This solution is awkward and wrong
• It is wrongfully associated only with Sailors,
  though it involves both Sailors and Boats.
• If Sailors is empty, the number of Boats tuples
  can be anything, since the conditional expression
  is not required to hold in such case!

7
General Constraints ASSERTION
Constraint Number of boats plus number of
sailors is lt 100
CREATE ASSERTION smallClub CHECK ( (SELECT
COUNT (S.sid) FROM Sailors S) (SELECT COUNT
(B.bid) FROM Boats B) lt 100 )

• The assertion is not associated with any one of
  the tables involved.
• ASSERTION is the right solution not associated
  with either table.

8
General Constraints Further Examples
Schema Students(sid int, sname string, age
int, cgpa real) Works(sid int, pid
int, pct_time int) Projects(did
int, budget real, ta int)

• Write SQL integrity constraints (domain, key,
  foreign key, or CHECK constraints or assertions)
  for given requirements
• Students must have a minimum cgpa of 5.
• Every TA must also be a student.
• The total percentage of all assignments to
  projects for a given student must be at most
  100.
• A TA must have a cgpa higher than any student
  that she coaches.

9
General Constraints Further Examples (1)
Constraint Students must have a minimum cgpa of
5. CREATE TABLE Students ( sid
INTEGER, sname CHAR(10), age REAL,
cgpa REAL, PRIMARY KEY
(sid), CHECK ( cgpa gt 5))
Constraint Every TA must also be a
student. CREATE ASSERTION TAisStudent CHECK
( (SELECT COUNT () FROM Projects P
WHERE P.ta_id NOT IN (SELECT sid
FROM Students)) 0 )
10
General Constraints Further Examples (2)
Constraint The total percentage of all
assignments to projects for a student must be
at most 100. CREATE TABLE Works ( sid
INTEGER, pid INTEGER, pct_time
INTEGER, PRIMARY KEY (sid,pid),
FOREIGN KEY (sid) REFERENCES Students,
FOREIGN KEY (pid) REFERENCES
Projects, CHECK ((SELECT COUNT (W.stid)
FROM Works W
GROUP BY W.stid
HAVING SUM(pct_time) gt 100) 0)
11
General Constraints Further Examples (3)
Constraint A TA must have a cgpa higher than any
student that she coaches.
CREATE ASSERTION TAHigherCGPA CHECK
((SELECT COUNT(S.stid) FROM Students S,
Students TA, Works W, Projects P WHERE
S.sidW.sid AND W.pidP.pid AND
P.taTA.sid AND S.cgpa gt TA.cgpa) 0)
12
Triggers

• Trigger procedure that starts automatically if
  specified changes occur to the DBMS
• Three parts
• Event (activates the trigger)
• Condition (tests whether the triggers should run)
• Action (what happens if the trigger runs)

13
Triggers

• BEFORE trigger action executed before the
  activating event occurs.
• AFTER trigger action executed after the
  activating event occurs.
• INSTEAD trigger action executed instead of the
  activating event.
• Row-level trigger executed once per modified row
  (that satisfies the trigger condition).
• Statement-level trigger executed once per
  modifying statement.
• Transition variables NEW, OLD, NEW TABLE, OLD
  TABLE.

14
Triggers Example (SQL92)
Increment a count for each newly inserted sailor
whose age lt 18.

• CREATE TRIGGER incr_count
• AFTER INSERT ON Sailors
• WHEN (new.age lt 18)
• FOR EACH ROW
• BEGIN
• countcount1
• END

• Illustrates use of NEW to refer to newly
  inserted tuples
• Exists since SQL92

15
Triggers Example (SQL1999)
Save newly inserted sailors aged lt 18 in a
special table.

• CREATE TRIGGER youngSailorsUpdate
• AFTER INSERT ON Sailors
• REFERENCING NEW TABLE AS NewSailors
• FOR EACH STATEMENT / This is the default /
• INSERT
• INTO YoungSailors(sid, name, age, rating)
• SELECT sid, name, age, rating
• FROM NewSailors N
• WHERE N.age lt 18

• Illustrates use of NEW TABLE to refer to a set
  of
• newly inserted tuples
• Exists since SQL1999

16
Triggers More Elaborated Example
Whenever a student is given a (bonus) raise on
his cgpa, the TAs cgpa must be increased to be
at least as high.

• CREATE TRIGGER bumpTAcgpa
• AFTER UPDATE ON Students
• WHEN OLD.cgpa lt NEW.cgpa
• FOR EACH ROW
• BEGIN
• UPDATE Students S
• SET S.cgpa NEW.cgpa
• WHERE S.cgpa lt NEW.cgpa
• AND S.sid IN (SELECT P.ta
• FROM STudents S1, Works W,
  Projects P
• WHERE S1.sid NEW.sid
• 
  AND S1.sid W.sid
• 
  AND W.sid P.sid)
• END

• Illustrates use of Oracle PL/SQL syntax in the
  action part.

17
Summary

• SQL allows specification of rich integrity
  constraints
• Triggers respond to changes in the database