SQL Integrity Constraints

1. SQL Integrity Constraints

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) • Commercial systems allow much more “fine-tuning” of constraints than do the modeling languages we learned so far

3. Types of Constraints • Covered so far • Domain Constraints (data type, UNIQUE, NOT NULL) • Primary key constraints • Foreign key constraints (= referential integrity) • General constraints • Attribute- and tuple-based checks • Constraints within a relation • Updates/inserts that violate constraints are rejected. • SQL2 Assertions: global constraints • Constraints can cover several relations • All modifications that violate constraint are rejected • Many systems do not support Assertions • SQL3 Triggers • Upon user specified modifications, checks are performed and adequate reactions are executed

4. Attribute-Based Checks • If a condition must hold for specific attribute: CHECK CREATE TABLE Sailors ( sid INTEGER PRIMARY KEY NOT NULL, sname VARCHAR(20), rating INTEGER CHECK(rating > 0 AND rating < 11), age INTEGER) • Condition is checked only when the associated attribute changes (i.e., an insert or update, but not delete!) • If condition is violated the system rejects the modification • In SQL condition can be anything that could follow WHERE clause • including subqueries • sname VARCHAR(20) CHECK (sname NOT IN (SELECT name FROM forbidden)) • DB2 allows very restricted attribute-based check (no subqueries, no reference to other attributes, …)

5. Tuple-Based Checks • If a condition covers several attributes CREATE TABLE Sailors ( sid INTEGER PRIMARY KEY NOT NULL, name VARCHAR(20), rating INTEGER, age INTEGER, CHECK (rating <= 6 OR age > 18)) • Checked upon each update and insert • SQL99 allows subqueries but many databases don’t

6. Naming constraints • Problem of previous examples: • what if constraints change (e.g., we want to increase rating constraint to (rating <=7 OR age > 18) • Solution: name constraints: CREATE TABLE Sailors ( sid INT, name VARCHAR(20), rating INT CONSTRAINT rat CHECK (rating > 0 AND rating < 11), age INT, CONSTRAINT pk PRIMARY KEY (sid), CONSTRAINT ratage CHECK (rating <= 6 OR age > 18)) • This allows us to drop and recreate them later on ALTER TABLE Sailors DROP CONSTRAINT ratage ALTER TABLE SAILORS ADD CONSTRAINT ratage CHECK (rating <=7 OR age > 18)

7. User defined Types • User can define new types from base types CREATE DISTINCT TYPE ratingval AS SMALLINT WITH COMPARISONS • The new types can be used for attribute definitions CREATE TABLE Sailors ( sid INTEGER PRIMARY KEY, name VARCHAR(20), rating ratingval, age SMALLINTEGER)

8. Comparison with user defined types • With Comparisons: • we can compare two attributes that are of the type ratingval SELECT S1.sid, S2.sid FROM Sailors S1, Sailors S2 WHERE S1.sid < S2.sid AND S1.rating = S2.rating • We can’t compare attributes with different type deriving from the same base type • Incorrect: SELECT sid FROM Sailors WHERE rating > age

9. Assertions • An assertion is a predicate expressing a condition that we wish the database always to satisfy. • Syntax: • CREATE ASSERTION ass-name CHECK (condition) • Reference to relations and attributes through SELECT statements CREATE ASSERTION smallClub CHECK ((SELECT COUNT (S.sid) FROM Sailors S) + (SELECT COUNT (B.bid) FROM Boats B) < 100) • Is checked whenever any of the relations involved is modified (delete/update/insert) • Especially useful if constraint covers more than one relation • CREATE TABLE Sailors • (… • CHECK ((SELECT COUNT (S.sid) FROM Sailors S) • + (SELECT COUNT (B.bid) FROM Boats B) < 100)) • Here, constraint is checked only when Sailors are included or change sid, but not, when Boats changes! • If bigClub (> 100), constraint should also be checked upon deletion; not done with CHECK

10. Comparison CHECK/ASSERTION • When activated • Attribute based checks: on insert of tuple or update of attribute • Tuple based checks: on insert or update of tuple • Assertion: on insert or update or delete of tuple in any involving relation • Constraint Guarantee • Attribute/tuple based checks: no, if containing subquery • Assertion: yes. • Costs: • Attribute/tuple: only evaluate constraint for tuple that is inserted/updated (cheap) • Assertion: evaluate whenever one of the involving relations changes (expensive)

11. Further Assertion Example CREATE ASSERTIONrating-constraintCHECK (NOT EXISTS (SELECT * FROM Reserves R, Sailors S, Boats BWHERE R.sid = S.sid AND B.bid = R.bid AND S.rating < 7 AND B.boattype = ‘luxury’))

12. Triggers • Trigger: procedure that starts automatically if specified changes occur to the DBMS • Three parts: • Event (activates the trigger): usually insert/update/delete • Condition (tests whether the trigger should run) • Action (what happens if the trigger runs) • Difference to Assertions and Checks: • When it is activated (the event) • Assertions etc.: upon any delete/update/insert • Triggers: user specifies the events that should fire the trigger: e.g., update of a specific attribute • What is done (the action) • Assertion etc.: reject modification • Triggers: Specify any action (sequence of SQL statements or reject) • Trigger introduced into SQL in 99, but many database systems supported it earlier: Syntax between DBS can vary considerably

13. Triggers: Example (SQL3) • Statement Level Trigger CREATE TRIGGER includeintoWorks_in AFTER INSERT ON Employees REFERENCING NEW_TABLE AS NewEmployees FOR EACH STATEMENT INSERT INTO StatisticsTable(ModTable, ModType, Count) SELECT ‘Employees’, ‘INSERT’, COUNT(*) FROM NewEmployees • Row Level Trigger CREATE TRIGGER salaryIncrease AFTER UPDATE OF salary on Employees REFERENCING OLD AS o, NEW AS n FOR EACH ROW WHEN (n.salary > 1.1 * o.salary) UPDATE Employees SET salary = 1.1 * o.salary WHERE eid = n.eid

14. Components of Triggers • Action can be executed before, after or instead of the triggering event. • AFTER UPDATE OF salary ON Employees (“of salary” optional) • BEFORE INSERT ON Employees • INSTEAD OF DELETE ON Employees • Action can refer to both the old and new values of tuples that were modified in the triggering event. • Row-level trigger: OLD/NEW references to old/new tuple • Old makes no sense in an insert, new makes no sense in a delete op. • Statement-level trigger: OLD_TABLE / NEW_TABLE • You can rename with the REFERENCING command • Condition in WHEN clause. Action is only executed if condition holds • Action performed • Row-level: once for each tuple changed in the triggering event • Statement-level: once for all tuples changed in the triggering event • Statement level can be more efficient if many tuples affected

15. More on Triggers • Activating before event can serve as extra constraints. CREATE TRIGGER setnull-trigger BEFORE UPDATE ON r REFERENCING NEW AS nrow FOR EACH ROW WHEN nrow.phone-number = ‘ ‘ SET nrow.phone-number = null • Action can also be an error: rollback CREATE TRIGGER salaryIncrease AFTER UPDATE OF salary on Employees REFERENCING OLD AS o, NEW AS n FOR EACH ROW WHEN (n.salary > 1.1 * o.salary) SIGNAL SQLSTATE ‘120’ SET MESSAGE_TEXT=‘Salary increase above 10%’

16. Several Actions • Use begin/end to encapsulate more than one action FOR EACH ROW/STATEMENT WHEN … BEGIN do 1thing; do 2nd thing END

17. External World Actions • We sometimes require external world actions to be triggered on a database update • E.g. re-ordering an item whose quantity in a warehouse has become small, or turning on an alarm light, • Triggers cannot directly implement external-world actions, BUT • Triggers can be used to record actions-to-be-taken (e.g., in extra table) • Have an external process that repeatedly scans the table, carries out external-world actions and deletes action from table • E.g. Suppose a warehouse has the following tables • inventory(item, level): How much of each item is in the warehouse • minlevel(item, level) : What is the minimum desired level of each item • reorder(item, amount): What quantity should we re-order at a time • orders(item, amount) : Orders to be placed (read by external process)

18. External Actions CREATE TRIGGER reorder-trigger AFTER UPDATE OF level ON inventory REFERENCING OLD ROW AS orow, NEW ROW AS nrow FOR EACH ROW WHEN nrow.level <= (SELECT level FROM minlevel WHERE minlevel.item = orow.item) AND orow.level > (SELECT level FROM minlevel WHERE minlevel.item = orow.item) BEGIN INSERT INTO orders (SELECT item, amount FROM reorder WHERE reorder.item = orow.item) END

19. DB2 specifics • For each row • For each statement • Begin atomic … end • Rollback work --> signal sqlstate ….

20. Other trigger use • Triggers are used for tasks such as • maintaining summary data (e.g. total salary of each departm.) • Replicating databases by recording changes to special relations (called change or delta relations) and having a separate process that applies the changes over to a replica • There are other ways of doing these now: • Databases today provide built in materialized view facilities to maintain summary data • Databases provide built-in support for replication • Encapsulation facilities can be used instead of triggers in many cases • Define methods to update fields • Carry out actions as part of the update methods instead of through a trigger

21. Application vs. Trigger • Pro Trigger • Trigger defined at database design and design expert knows about constraints • Despite different application constraints, database consistency maintained • speed • Cons Trigger • If there are several triggers on same update things can become messy (what if trigger action triggers another trigger….) • If important application semantic implemented as triggers, then split of application logic • Part in application program / part as trigger • difficult to manage