CS411 Database Systems Foreign Keys Local and Global Constraints - PDF document

Constraints & Triggers CS411 Database Systems Foreign Keys Local and Global Constraints 06: SQL Triggers Kazuhiro Minami Integrity Constraints in SQL Constraints and Triggers • New information added to a database could be wrong in a variety of ways • A constraint is a relationship among – Typographical or transcription errors in manually data elements that the DBMS is required entered data to enforce. • Difficult to write application programs to check – Example: key constraints. the integrity (correctness) of data on every • Triggers are only executed when a insertion, deletion, and update command. specified condition occurs, e.g., insertion • SQL provides a variety of techniques for of a tuple. expressing integrity constraints as part of the – Easier to implement than many constraints. database schema

Kinds of Constraints Foreign Keys Sells(bar, beer, price) • Keys • We might expect that a beer value is a • Foreign-key, or referential-integrity real beer --- something appearing in • Value-based constraints Beers.name . – Constrain values of a particular attribute • A constraint that requires a beer in Sells • Tuple-based constraints to be a beer in Beers is called a foreign - – Relationship among different attribute values key (referential integrity) constraint. • Assertions: any SQL boolean expression Foreign-key Constraints Corresponds Example to Referential Integrity Constraints in E/R modeling Beers Sells bar beer price name manf Super Dry Asahi Blind pig Super Dry $3 Blind pig Samuel Adams $4 sells Sells Beers Violation of the foreign-key constraint

Expressing Foreign Keys Example: With Attribute • Use the keyword REFERENCES, either: Referenced attribute 1. Within the declaration of an attribute, when only one attribute is involved. CREATE TABLE Beers ( REFERENCES <relation> ( <attributes> ) name CHAR(20) PRIMARY KEY, manf CHAR(20) ); 1. As an element of the schema, as: FOREIGN KEY ( <list of attributes> ) CREATE TABLE Sells ( REFERENCES <relation> ( <attributes> ) barCHAR(20), • Referenced attributes must be declared beer CHAR(20) REFERENCES Beers(name), PRIMARY KEY or UNIQUE price REAL ); Foreign key Enforcing Foreign-Key Constraints Example: As Element If there is a foreign-key constraint from CREATE TABLE Beers ( attributes of relation R to the primary key name CHAR(20) PRIMARY KEY, of relation S , two violations are possible: manf CHAR(20) ); 1. An insert or update to R introduces values not found in S . CREATE TABLE Sells ( bar CHAR(20), 2. A deletion or update to S causes some tuples of R to “dangle.” beer CHAR(20), price REAL, foreign Referenced FOREIGN KEY(beer) REFERENCES Beers(name)); key attributes R S Foreign-key definition

Case 1: Insertion or Update to R Actions Taken -- 1 Beers (= S) Sells (= R) • An insert or update to Sells that introduces a nonexistent beer must bar beer price name manf be rejected. Blind pig Super Dry $3 Super Dry Asahi Blind pig Samuel Adams $4 Dangling tuple Case 2: Deletion or Update to Actions Taken -- 2 S Beers (= S) Sells (= R) The three possible ways to handle beers that suddenly cease to exist are: bar beer price name manf Blind pig Super Dry $3 Super Dry Asahi 1. Default : Reject the modification. Samuel Adams The Boston 2. Cascade : Make the same changes in Blind pig Samuel Adams $4 Sells. Beer Company - Deleted beer: delete Sells tuple. - Updated beer: change value in Sells. The second tuple in Sells has 3. Set NULL : Change the beer to NULL. become dangle.

Cascade Strategy Example: Cascade Beers Sells • Suppose we delete the Bud tuple from Beers. bar beer price name manf – Then delete all tuples from Sells that have Blind pig Super Dry $3 Bitter Super Dry Asahi Bitter beer = ’Bud’. Samuel Adams The Boston Blind pig Samuel Adams $4 • Suppose we update the Bud tuple by Beer Company changing ’Bud’ to ’Budweiser’. – Then change all Sells tuples with beer = ’Bud’ so that beer = ’Budweiser’. Example: Set NULL Example: Set NULL Beers Sells • Suppose we delete the Bud tuple from Beers. bar beer price name manf – Change all tuples of Sells that have beer = Blind pig Super Dry $3 NULL Super Dry Asahi Bitter ’Bud’ to have beer = NULL. Samuel Adams The Boston NULL Blind pig Samuel Adams $4 • Suppose we update the Bud tuple by Beer Company changing ’Bud’ to ’Budweiser’. – Same change.

Attribute-Based Checks: When you create the table, specify which You can also check an attribute value of the three options you want to use. at INSERT/UPDATE time CREATE TABLE Customers ( CREATE TABLE Customers ( Use a subquery if customerName CHAR(20) REFERENCES MasterList(name) customerName CHAR(20) REFERENCES MasterList(name) you need to mention ON DELETE CASCADE, ON DELETE CASCADE, other attributes or CREATE TABLE Sells ( city CHAR(20), relations city CHAR(20), Default: reject all bar CHAR(20), state CHAR(2), UPDATEs to MasterList state CHAR(2), that violate referential zip CHAR (5), zip CHAR (5), beer CHAR(20) CHECK ( beer IN integrity FOREIGN KEY (city, state, zip) FOREIGN KEY (city, state, zip) (SELECT name FROM Beers)), REFERENCES GoodAddresses (city, state, zip) REFERENCES GoodAddresses (city, state, zip) price REAL CHECK ( price <= 5.00 ) ON UPDATE CASCADE ON DELETE SET NULL ON UPDATE CASCADE ON DELETE SET NULL ); ); ); CHECK is never equivalent to a Tuple-Based Checks: foreign key constraint. You can also check a combination of Departments attribute values at INSERT/UPDATE time Employees Name Toy Employee Department Hourly Name Wage Complaint • Only Joe’s Bar can sell beer for more than $5: Winslett Toy 10.00 Development With a FOREIGN KEY CREATE TABLE Sells ( constraint, the change in bar CHAR(20), Departments will be reflected in beer CHAR(20), Name Employees. priceREAL, Complaint CHECK (bar = ’Joe’’s Bar’ OR Development price <= 5.00) ); th CHECK , Wi With , th the e chan ange in D ge in Departmen partments w s will ill not b t be ref reflected ected in Emp Employ oyees ees.

For more complex constraints, There cannot be more bars declare standalone ASSERTIONs. than drinkers. CREATE ASSERTION assertionName Drinkers(name, addr, phone) CHECK ( condition ); Bars(name, addr, license) No bar can charge more than $5 on average for beer. CREATE ASSERTION NoExpensiveBars CHECK ( CREATE ASSERTION NoExpensiveBars CHECK ( NOT EXISTS ( NOT EXISTS ( CREATE ASSERTION FewBars CHECK ( CREATE ASSERTION FewBars CHECK ( Bars with an SELECT bar SELECT bar (SELECT COUNT (*) FROM Bars) <= average price (SELECT COUNT (*) FROM Bars) <= FROM Sells FROM Sells above $5 (SELECT COUNT (*) FROM DRINKERS) (SELECT COUNT (*) FROM DRINKERS) GROUP BY bar GROUP BY bar ); HAVING 5.00 < AVG(price) ); HAVING 5.00 < AVG(price) )); )); In theory, every ASSERTION is You can help your not-so-smart DBMS checked after every INSERT/ by using TRIGGERs instead of DELETE/ UPDATE. ASSERTIONS. E.g., an INSERT / DELETE / UPDATE In practice, the DBMS only has to check to relation R A trigger is an ECA rule: sometimes: • Adding a drinker can’t violate FewBars. When Event occurs Any SQL Boolean • Removing a bar can’t violate NoExpensiveBars. condition If Condition doesn’t hold • Lowering a beer price can’t violate NoExpensiveBars. Then do Action But is the DBMS s But is the DBMS smart enough art enough to fi to figure th gure this is out? out? Any SQL statements

If someone inserts an unknown beer into You can use triggers to code Sells(bar, beer, price), add it to Beers very complex stuff. with a NULL manufacturer. CREATE TRIGGER BeerTrig The event • You can allow your users to update their views AFTER INSERT ON Sells --- but you catch their updates and rewrite them to behave the way you want, avoiding REFERENCING NEW ROW AS NewTuple view anomalies. FOR EACH ROW • You can encode new strategies for handling WHEN (NewTuple.beer NOT IN The condition violations of constraints, different from what (SELECT name FROM Beers)) the DBMS offers. INSERT INTO Beers(name) The action VALUES(NewTuple.beer); Syntax for naming the trigger Syntax for describing the condition Take one element from each of the three columns: CREATE TRIGGER name INSERT BEFORE DELETE ON relationName AFTER CREATE OR REPLACE TRIGGER name UPDATE INSTEAD OF Useful when there is a trigger with that name and UPDATE ON attribute you want to modify the trigger. Only if the relation is a view