CHAPTER7-Constraints and Triggers

Click to edit Master text styles,Second level,Third level,Fourth level,Fifth level,Click to edit Master title style,Chapter7 Constraints and Triggers,Constraints and Triggers,A,constraint,is a relationship among data elements that the DBMS is required to enforce.,Example:,key constraints.,Triggers,are only executed when a specified condition occurs, e.g., insertion of a tuple.,Easier to implement than complex constraints.,Kinds of Constraints,Keys.,Foreign-key, or referential-integrity.,Value-based,constraints.,Constrain values of a particular attribute.,Tuple-based,constraints.,Relationship among components.,Assertions:,any SQL boolean expression.,Review,: Single-Attribute Keys,Place PRIMARY KEY or UNIQUE after the type in the declaration of the attribute.,Example:,CREATE TABLE Beers (,nameCHAR(20) UNIQUE,manfCHAR(20),);,Review,: Multiattribute Key,The bar and beer together are the key for Sells:,CREATE TABLE Sells (,barCHAR(20),beerVARCHAR(20),priceREAL,PRIMARY KEY (bar, beer),);,Foreign Keys,Values appearing in attributes of one relation must appear together in certain attributes of another relation.,Example,: in,Sells(bar, beer, price), we might expect that a beer value also appears in Beers.name .,Expressing Foreign Keys,Use keyword REFERENCES, either:,After an attribute (for one-attribute keys).,As an element of the schema:,FOREIGN KEY (),REFERENCES (),Referenced attributes must be declared PRIMARY KEY or UNIQUE.,Example,: With Attribute,CREATE TABLE Beers (,nameCHAR(20) PRIMARY KEY,manfCHAR(20) );,CREATE TABLE Sells (,barCHAR(20),beerCHAR(20) REFERENCES Beers(name),priceREAL );,Example,: As Schema Element,CREATE TABLE Beers (,nameCHAR(20) PRIMARY KEY,manfCHAR(20) );,CREATE TABLE Sells (,barCHAR(20),beerCHAR(20),priceREAL,FOREIGN KEY(beer) REFERENCES Beers(name);,Enforcing Foreign-Key Constraints,If there is a foreign-key constraint from relation,R,to relation,S, two violations are possible:,An insert or update to,R,introduces values not found in,S,.,A deletion or update to S causes some,tuples,of,R,to “dangle.”,Actions Taken - (1),Example:,suppose,R,= Sells,S,= Beers.,An insert or update to,Sells,that introduces a nonexistent beer must be rejected.,A deletion or update to,Beers,that removes a beer value found in some tuples of,Sells,can be handled in three ways (next slide).,Actions Taken - (2),Default,: Reject the modification.,Cascade(,级联、串联,),: Make the same changes in Sells.,Deleted beer,: delete Sells,tuple,.,Updated beer,: change value in Sells.,Set NULL,: Change the sells to NULL.,Example,: Cascade,Delete the Bud tuple from Beers:,Then delete all tuples from Sells that have beer = Bud.,Update the Bud tuple by changing Bud to Budweiser:,Then change all Sells tuples with beer = Bud to beer = Budweiser.,Example,: Set NULL,Delete the Bud tuple from Beers:,Change all tuples of Sells that have beer = Bud to have beer = NULL.,Update the Bud tuple by changing Bud to Budweiser:,Same change as for deletion.,Choosing a Policy,When we declare a foreign key, we may choose policies SET NULL or CASCADE independently for deletions and updates.,Follow the foreign-key declaration by:,ON UPDATE, DELETESET NULL CASCADE,Two such clauses may be used.,Otherwise, the default (reject) is used.,Example,: Setting Policy,CREATE TABLE Sells (,barCHAR(20),beerCHAR(20),priceREAL,FOREIGN KEY(beer),REFERENCES Beers(name),ON DELETE SET NULL,ON UPDATE CASCADE,);,Attribute-Based Checks,Constraints on the value of a particular attribute.,Add CHECK() to the declaration for the attribute.,The condition may use the name of the attribute, but,any other relation or attribute name must be in a subquery,.,Example,: Attribute-Based Check,CREATE TABLE Sells (,barCHAR(20),beerCHAR(20)CHECK ( beer IN,(SELECT name FROM Beers),priceREAL CHECK ( price = 5.00 ),);,Timing of Checks,Attribute-based checks are performed only when a value for that attribute is inserted or updated.,Example,:,CHECK (price = 5.00),checks every new price and rejects the modification (for that tuple) if the price is more than $5.,Example,:,CHECK (beer IN (SELECT name FROM Beers),not checked if a beer is deleted from Beers (unlike foreign-keys).,Tuple-Based Checks,CHECK () may be added as a relation-schema element.,The condition may refer to any attribute of the relation.,But other attributes or relations require a subquery.,Checked on insert or update only.,Example,: Tuple-Based Check,Only Joes Bar can sell beer for more than $5:,CREATE TABLE Sells (,barCHAR(20),beerCHAR(20),priceREAL,CHECK (bar = Joes Bar OR,price = 5.00),);,Assertions,These are database-schema elements, like relations or views.,Defined by:,CREATE ASSERTION ,CHECK ();,Condition may refer to any relation or attribute in the database schema.,Example,: Assertion,In,Sells(bar, beer, price), no bar may charge an average of more than $5.,CREATE ASSERTION NoRipoffBars CHECK (,NOT EXISTS (,SELECT bar FROM Sells,GROUP BY bar,HAVING 5.00 AVG(price),);,Bars with an,average price,above $5,Example,: Assertion,In,Drinkers(name, addr, phone),and,Bars(name, addr, license), there cannot be more bars than drinkers.,CREATE ASSERTION FewBar CHECK (,(SELECT COUNT(*) FROM Bars) ooo.price + 1.00),INSERT INTO RipoffBars,VALUES(nnn.bar);,The event ,only changes,to prices,Updates let us,talk about old,and new tuples,We need to consider,each price change,Condition:,a raise in,price $1,When the price change,is great enough, add,the bar to RipoffBars,