Transcript ch5_sql_csi3317

SQL: Constraints and Triggers
Chapter 5, 5.7-5.8
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
1
Integrity Constraints (Review)

An IC describes conditions that every legal instance
of a relation must satisfy.



Inserts/deletes/updates that violate IC’s 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 < 200)
Types of IC’s: Domain constraints, primary key
constraints, foreign key constraints, general
constraints.

Domain constraints: Field values must be of right type.
Always enforced.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
2
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
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
3
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 >= 1 AND rating <= 10 ))
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
4
CHECK Constraints: Examples
Constraint: Interlake boats cannot be reserved
CREATE TABLE Reserves
( sname CHAR(10),
bid INTEGER,
day DATE,
PRIMARY KEY (bid,day),
CONSTRAINT noInterlakeRes
CHECK (`Interlake’ <>
( SELECT B.bname
FROM Boats B
WHERE B.bid=bid)))
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
5
General Constraints: ASSERTION
Constraint: Number of boats plus number of sailors is < 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) < 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!
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
6
General Constraints: ASSERTION
Constraint: Number of boats plus number of sailors is < 100
CREATE ASSERTION smallClub
CHECK
( (SELECT COUNT (S.sid) FROM Sailors S)
+ (SELECT COUNT (B.bid) FROM Boats B) < 100 )


The assertion is not associated with any one of the tables
involved.
ASSERTION is the right solution; not associated with either
table.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
7
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.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
8
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 >= 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 )
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
9
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) > 100) = 0)
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
10
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.sid=W.sid
AND W.pid=P.pid
AND P.ta=TA.sid
AND S.cgpa > TA.cgpa) =0)
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
11
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)
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
12
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.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
13
Triggers: Example (SQL:92)
Increment a count for each newly inserted sailor whose age < 18.
CREATE TRIGGER incr_count
AFTER INSERT ON Sailors
WHEN (new.age < 18)
FOR EACH ROW
BEGIN
count:=count+1;
END
 Illustrates use of NEW to refer to newly inserted tuples
 Exists since SQL:92
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
14
Triggers: Example (SQL:1999)
Save newly inserted sailors aged < 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 <= 18
 Illustrates use of NEW TABLE to refer to a set of
newly inserted tuples
 Exists since SQL:1999
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
15
Triggers: More Elaborated Example
Whenever a student is given a (bonus) raise on his cgpa,
the TA’s cgpa must be increased to be at least as high.
CREATE TRIGGER bumpTAcgpa
AFTER UPDATE ON Students
WHEN OLD.cgpa < NEW.cgpa
FOR EACH ROW
BEGIN
UPDATE Students S
SET S.cgpa = NEW.cgpa
WHERE S.cgpa < 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.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
16
Summary
SQL allows specification of rich integrity
constraints
 Triggers respond to changes in the database

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
17