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

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 >= 1 AND rating <= 10 ))

5. CREATE TABLE Reserves ( sname CHAR(10), bid INTEGER, day DATE, PRIMARY KEY (bid,day), CONSTRAINTnoInterlakeRes CHECK (`Interlake’ <> ( SELECT B.bname FROM Boats B WHERE B.bid=bid))) CHECK Constraints: Examples Constraint: Interlake boats cannot be reserved

6. 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!

7. General Constraints: ASSERTION Constraint: Number of boats plus number of sailors is < 100 • The assertion is not associated with any one of the tables involved. • ASSERTION is the right solution; not associated with either table. CREATE ASSERTION smallClub CHECK ( (SELECT COUNT (S.sid) FROM Sailors S) + (SELECT COUNT (B.bid) FROM Boats B) < 100 )

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 >= 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) > 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.sid=W.sid AND W.pid=P.pid AND P.ta=TA.sid AND S.cgpa > 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 (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

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

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

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