SQL Integrity Constraints Integrity Constraints Review q An

SQL Integrity Constraints

Integrity Constraints (Review) q An IC describes conditions that every legal instance of a relation must satisfy. Inserts/deletes/updates that violate IC’s are disallowed. I 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) I q Commercial systems allow much more “finetuning” of constraints than do the modeling languages we learned so far 421 B: Database Systems - Integrity Constraints 2

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

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) q Condition is checked only when the associated attribute changes (i. e. , an insert or update, but not delete!) q If condition is violated the system rejects the modification q In SQL condition can be anything that could follow WHERE clause q I including subqueries I sname VARCHAR(20) CHECK (sname NOT IN (SELECT name FROM forbidden)) q DB 2 allows very restricted attribute-based check (no subqueries, no reference to other attributes, …) 421 B: Database Systems - Integrity Constraints 4

Tuple-Based Checks q 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)) r Checked upon each update and insert r SQL 99 allows subqueries but many databases don’t 421 B: Database Systems - Integrity Constraints 5

Naming constraints q Problem of previous examples: I what if constraints change (e. g. , we want to increase rating constraint to (rating <=7 OR age > 18) q 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)) q 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) 421 B: Database Systems - Integrity Constraints 6

User defined Types q User can define new types from base types CREATE DISTINCT TYPE ratingval AS SMALLINT WITH COMPARISONS q The new types can be used for attribute definitions CREATE TABLE Sailors ( sid INTEGER PRIMARY KEY, name VARCHAR(20), ratingval, age SMALLINTEGER) 421 B: Database Systems - Integrity Constraints 7

Comparison with user defined types q With Comparisons: I we can compare two attributes that are of the type ratingval SELECT S 1. sid, S 2. sid FROM Sailors S 1, Sailors S 2 WHERE S 1. sid < S 2. sid AND S 1. rating = S 2. rating q We can’t compare attributes with different type deriving from the same base type I Incorrect: SELECT sid FROM Sailors WHERE rating > age 421 B: Database Systems - Integrity Constraints 8

Assertions q q An assertion is a predicate expressing a condition that we wish the database always to satisfy. Syntax: I CREATE ASSERTION ass-name CHECK (condition) I Reference to relations and attributes through SELECT statements CREATE ASSERTION small. Club 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) q Especially useful if constraint covers more than one relation q • 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 big. Club (> 100), constraint should also be checked upon deletion; not done with CHECK 421 B: Database Systems - Integrity Constraints 9

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

Further Assertion Example CREATE ASSERTION rating-constraint CHECK (NOT EXISTS (SELECT * FROM Reserves R, Sailors S, Boats B WHERE R. sid = S. sid AND B. bid = R. bid AND S. rating < 7 AND B. boattype = ‘luxury’)) 421 B: Database Systems - Integrity Constraints 11

Triggers Trigger: procedure that starts automatically if specified changes occur to the DBMS q Three parts: I Event (activates the trigger): usually insert/update/delete I Condition (tests whether the trigger should run) I Action (what happens if the trigger runs) q Difference to Assertions and Checks: I When it is activated (the event) q l l I What is done (the action) l l q Assertions etc. : upon any delete/update/insert Triggers: user specifies the events that should fire the trigger: e. g. , update of a specific attribute 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 421 B: Database Systems - Integrity Constraints 12

Triggers: Example (SQL 3) q Statement Level Trigger CREATE TRIGGER includeinto. Works_in AFTER INSERT ON Employees REFERENCING NEW_TABLE AS New. Employees FOR EACH STATEMENT INSERT INTO Statistics. Table(Mod. Table, Mod. Type, Count) SELECT ‘Employees’, ‘INSERT’, COUNT(*) FROM New. Employees q Row Level Trigger CREATE TRIGGER salary. Increase 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 421 B: Database Systems - Integrity Constraints 13

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

More on Triggers q 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 q Action can also be an error: rollback CREATE TRIGGER salary. Increase 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%’ 421 B: Database Systems - Integrity Constraints 15

Several Actions q Use begin/end to encapsulate more than one action FOR EACH ROW/STATEMENT WHEN … BEGIN do 1 thing; do 2 nd thing END 421 B: Database Systems - Integrity Constraints 16

External World Actions q We sometimes require external world actions to be triggered on a database update I E. g. re-ordering an item whose quantity in a warehouse has become small, or turning on an alarm light, q Triggers cannot directly implement external-world actions, BUT I Triggers can be used to record actions-to-be-taken (e. g. , in extra table) I Have an external process that repeatedly scans the table, carries out external-world actions and deletes action from table q E. g. Suppose a warehouse has the following tables I I 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) 421 B: Database Systems - Integrity Constraints 17

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 421 B: Database Systems - Integrity Constraints 18

DB 2 specifics q For each row q For each statement q Begin atomic … end q Rollback work --> signal sqlstate …. 421 B: Database Systems - Integrity Constraints 19

Other trigger use q Triggers are used for tasks such as I maintaining summary data (e. g. total salary of each departm. ) I 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 q There are other ways of doing these now: I Databases today provide built in materialized view facilities to maintain summary data I Databases provide built-in support for replication q Encapsulation facilities can be used instead of triggers in many cases I Define methods to update fields I Carry out actions as part of the update methods instead of through a trigger 421 B: Database Systems - Integrity Constraints 20

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