constraints
DESCRIPTION
Constraints. Foreign Keys Local and Global Constraints Triggers. Source: slides by Jeffrey Ullman. Constraints and Triggers. A constraint is a relationship among data elements that the DBMS is required to enforce. Example: key constraints. - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: Constraints](https://reader035.vdocuments.us/reader035/viewer/2022062304/5681332a550346895d9a19f4/html5/thumbnails/1.jpg)
1
Constraints
Foreign KeysLocal and Global Constraints
Triggers
Source: slides by Jeffrey Ullman
![Page 2: Constraints](https://reader035.vdocuments.us/reader035/viewer/2022062304/5681332a550346895d9a19f4/html5/thumbnails/2.jpg)
2
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.
![Page 3: Constraints](https://reader035.vdocuments.us/reader035/viewer/2022062304/5681332a550346895d9a19f4/html5/thumbnails/3.jpg)
3
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.
![Page 4: Constraints](https://reader035.vdocuments.us/reader035/viewer/2022062304/5681332a550346895d9a19f4/html5/thumbnails/4.jpg)
4
Foreign Keys
Consider Relation Sells(store, candy, price).
We might expect that a candy value is a real candy --- something appearing in Candies.name .
A constraint that requires a candy in Sells to be a candy in Candies is called a foreign -key constraint.
![Page 5: Constraints](https://reader035.vdocuments.us/reader035/viewer/2022062304/5681332a550346895d9a19f4/html5/thumbnails/5.jpg)
5
Expressing Foreign Keys
Use the keyword REFERENCES, either: Within the declaration of an attribute (only
for one-attribute keys). As an element of the schema:
FOREIGN KEY ( <list of attributes> )REFERENCES <relation>
( <attributes> ) Referenced attributes must be declared
PRIMARY KEY or UNIQUE.
![Page 6: Constraints](https://reader035.vdocuments.us/reader035/viewer/2022062304/5681332a550346895d9a19f4/html5/thumbnails/6.jpg)
6
Example: With Attribute
CREATE TABLE Candies (nameCHAR(20) PRIMARY KEY,manfCHAR(20) );
CREATE TABLE Sells (store CHAR(20),candy CHAR(20) REFERENCES
Candies(name),price REAL );
![Page 7: Constraints](https://reader035.vdocuments.us/reader035/viewer/2022062304/5681332a550346895d9a19f4/html5/thumbnails/7.jpg)
7
Example: As ElementCREATE TABLE Candies (nameCHAR(20) PRIMARY KEY,manfCHAR(20) );
CREATE TABLE Sells (store CHAR(20),candy CHAR(20),price REAL,FOREIGN KEY(candy) REFERENCES
Candies(name));
![Page 8: Constraints](https://reader035.vdocuments.us/reader035/viewer/2022062304/5681332a550346895d9a19f4/html5/thumbnails/8.jpg)
8
Enforcing Foreign-Key Constraints
If there is a foreign-key constraint from attributes of relation R to a key of 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.”
![Page 9: Constraints](https://reader035.vdocuments.us/reader035/viewer/2022062304/5681332a550346895d9a19f4/html5/thumbnails/9.jpg)
9
Actions Taken --- (1)
Suppose R = Sells, S = Candies. An insert or update to Sells that
introduces a nonexistent candy must be rejected.
A deletion or update to Candies that removes a candy value found in some tuples of Sells can be handled in three ways (next slide).
![Page 10: Constraints](https://reader035.vdocuments.us/reader035/viewer/2022062304/5681332a550346895d9a19f4/html5/thumbnails/10.jpg)
10
Actions Taken --- (2)
Default : Reject the modification. Cascade : Make the same changes
in Sells. Deleted candy: delete Sells tuple. Updated candy: change value in Sells.
Set NULL : Change the candy to NULL.
![Page 11: Constraints](https://reader035.vdocuments.us/reader035/viewer/2022062304/5681332a550346895d9a19f4/html5/thumbnails/11.jpg)
11
Example: Cascade
Delete the Twizzler tuple from Candies: Then delete all tuples from Sells that
have candy = ’Twizzler’. Update the Twizzler tuple by
changing ’Twizzler’ to ’Twiz.’: Then change all Sells tuples with candy
= ’Twizzler’ so that candy = ’Twiz.’.
![Page 12: Constraints](https://reader035.vdocuments.us/reader035/viewer/2022062304/5681332a550346895d9a19f4/html5/thumbnails/12.jpg)
12
Example: Set NULL
Delete the Twizzler tuple from Candies: Change all tuples of Sells that have
candy = ’Twizzler’ to have candy = NULL.
Update the Twizzler tuple by changing ’Twizzler’ to ’Twiz.’: Same change.
![Page 13: Constraints](https://reader035.vdocuments.us/reader035/viewer/2022062304/5681332a550346895d9a19f4/html5/thumbnails/13.jpg)
13
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, DELETE][SET NULL, CASCADE] Two such clauses may be used. Otherwise, the default (reject) is used.
![Page 14: Constraints](https://reader035.vdocuments.us/reader035/viewer/2022062304/5681332a550346895d9a19f4/html5/thumbnails/14.jpg)
14
Example
CREATE TABLE Sells (store CHAR(20),candy CHAR(20),price REAL,FOREIGN KEY(candy)
REFERENCES Candies(name)ON DELETE SET NULLON UPDATE CASCADE
);
![Page 15: Constraints](https://reader035.vdocuments.us/reader035/viewer/2022062304/5681332a550346895d9a19f4/html5/thumbnails/15.jpg)
15
Attribute-Based Checks
Constraints on the value of a particular attribute.
Add: CHECK( <condition> ) 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.
![Page 16: Constraints](https://reader035.vdocuments.us/reader035/viewer/2022062304/5681332a550346895d9a19f4/html5/thumbnails/16.jpg)
16
Example
CREATE TABLE Sells (
store CHAR(20),
candy CHAR(20) CHECK ( candy IN
(SELECT name FROM Candies)),
price REAL CHECK ( price <= 5.00 )
);
![Page 17: Constraints](https://reader035.vdocuments.us/reader035/viewer/2022062304/5681332a550346895d9a19f4/html5/thumbnails/17.jpg)
17
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 (candy IN (SELECT name FROM Candies))is not checked if a candy is deleted from Candies (unlike foreign-keys).
![Page 18: Constraints](https://reader035.vdocuments.us/reader035/viewer/2022062304/5681332a550346895d9a19f4/html5/thumbnails/18.jpg)
18
Tuple-Based Checks
CHECK ( <condition> ) may be added as a relation-schema element.
The condition may refer to any attribute of the relation. But any other attributes or relations
require a subquery. Checked on insert or update only.
![Page 19: Constraints](https://reader035.vdocuments.us/reader035/viewer/2022062304/5681332a550346895d9a19f4/html5/thumbnails/19.jpg)
19
Example: Tuple-Based Check
Only 7-11 can sell candy for more than $5:CREATE TABLE Sells (
store CHAR(20),candy CHAR(20),price REAL,CHECK (store = ’7-11’ OR
price <= 5.00));
![Page 20: Constraints](https://reader035.vdocuments.us/reader035/viewer/2022062304/5681332a550346895d9a19f4/html5/thumbnails/20.jpg)
20
Assertions
These are database-schema elements, like relations or views.
Defined by:CREATE ASSERTION <name>
CHECK ( <condition> ); Condition may refer to any relation
or attribute in the database schema.
![Page 21: Constraints](https://reader035.vdocuments.us/reader035/viewer/2022062304/5681332a550346895d9a19f4/html5/thumbnails/21.jpg)
21
Example: Assertion
In Sells(store, candy, price), no store may charge an average of more than $5.
CREATE ASSERTION NoRipoffStores CHECK (NOT EXISTS (
SELECT store FROM SellsGROUP BY storesHAVING AVG(price) > 5.00
));
Stores with anaverage priceabove $5
![Page 22: Constraints](https://reader035.vdocuments.us/reader035/viewer/2022062304/5681332a550346895d9a19f4/html5/thumbnails/22.jpg)
22
Example: Assertion
In Consumers(name, addr, phone) and Stores(name, addr, license), there cannot be more stores than consumers.
CREATE ASSERTION FewStore CHECK (
(SELECT COUNT(*) FROM Stores) <=
(SELECT COUNT(*) FROM Consumers)
);
![Page 23: Constraints](https://reader035.vdocuments.us/reader035/viewer/2022062304/5681332a550346895d9a19f4/html5/thumbnails/23.jpg)
23
Timing of Assertion Checks
In principle, we must check every assertion after every modification to any relation of the database.
A clever system can observe that only certain changes could cause a given assertion to be violated. Example: No change to Candies can
affect FewStore. Neither can an insertion to Consumers.
![Page 24: Constraints](https://reader035.vdocuments.us/reader035/viewer/2022062304/5681332a550346895d9a19f4/html5/thumbnails/24.jpg)
24
Triggers: Motivation
Assertions are powerful, but the DBMS often can’t tell when they need to be checked.
Attribute- and tuple-based checks are checked at known times, but are not powerful.
Triggers let the user decide when to check for a powerful condition.
![Page 25: Constraints](https://reader035.vdocuments.us/reader035/viewer/2022062304/5681332a550346895d9a19f4/html5/thumbnails/25.jpg)
25
Event-Condition-Action Rules
Another name for “trigger” is ECA rule, or event-condition-action rule.
Event : typically a type of database modification, e.g., “insert on Sells.”
Condition : Any SQL boolean-valued expression.
Action : Any SQL statements.
![Page 26: Constraints](https://reader035.vdocuments.us/reader035/viewer/2022062304/5681332a550346895d9a19f4/html5/thumbnails/26.jpg)
26
Preliminary Example: A Trigger
Instead of using a foreign-key constraint and rejecting insertions into Sells(store, candy, price) with unknown candies, a trigger can add that candy to Candies, with a NULL manufacturer.
![Page 27: Constraints](https://reader035.vdocuments.us/reader035/viewer/2022062304/5681332a550346895d9a19f4/html5/thumbnails/27.jpg)
27
Example: Trigger Definition
CREATE TRIGGER CandyTrigAFTER INSERT ON SellsREFERENCING NEW ROW AS NewTupleFOR EACH ROWWHEN (NewTuple.candy NOT IN
(SELECT name FROM Candies))INSERT INTO Candies(name)
VALUES(NewTuple.candy);
The event
The condition
The action
![Page 28: Constraints](https://reader035.vdocuments.us/reader035/viewer/2022062304/5681332a550346895d9a19f4/html5/thumbnails/28.jpg)
28
Options: CREATE TRIGGER
CREATE TRIGGER <name> Option:CREATE OR REPLACE TRIGGER
<name> Useful if there is a trigger with that
name and you want to modify the trigger.
![Page 29: Constraints](https://reader035.vdocuments.us/reader035/viewer/2022062304/5681332a550346895d9a19f4/html5/thumbnails/29.jpg)
29
Options: The Event
AFTER can be BEFORE. Also, INSTEAD OF, if the relation is a
view.• A great way to execute view modifications:
have triggers translate them to appropriate modifications on the base tables.
INSERT can be DELETE or UPDATE. And UPDATE can be UPDATE . . . ON a
particular attribute.
![Page 30: Constraints](https://reader035.vdocuments.us/reader035/viewer/2022062304/5681332a550346895d9a19f4/html5/thumbnails/30.jpg)
30
Options: FOR EACH ROW
Triggers are either “row-level” or “statement-level.”
FOR EACH ROW indicates row-level; its absence indicates statement-level.
Row level triggers : execute once for each modified tuple.
Statement-level triggers : execute once for an SQL statement, regardless of how many tuples are modified.
![Page 31: Constraints](https://reader035.vdocuments.us/reader035/viewer/2022062304/5681332a550346895d9a19f4/html5/thumbnails/31.jpg)
31
Options: REFERENCING
INSERT statements imply a new tuple (for row-level) or new table (for statement-level). The “table” is the set of inserted tuples.
DELETE implies an old tuple or table. UPDATE implies both. Refer to these by[NEW OLD][TUPLE TABLE] AS <name>
![Page 32: Constraints](https://reader035.vdocuments.us/reader035/viewer/2022062304/5681332a550346895d9a19f4/html5/thumbnails/32.jpg)
32
Options: The Condition
Any boolean-valued condition is appropriate.
It is evaluated before or after the triggering event, depending on whether BEFORE or AFTER is used in the event.
Access the new/old tuple or set of tuples through the names declared in the REFERENCING clause.
![Page 33: Constraints](https://reader035.vdocuments.us/reader035/viewer/2022062304/5681332a550346895d9a19f4/html5/thumbnails/33.jpg)
33
Options: The Action
There can be more than one SQL statement in the action. Surround by BEGIN . . . END if there is
more than one. But queries make no sense in an
action, so we are really limited to modifications.
![Page 34: Constraints](https://reader035.vdocuments.us/reader035/viewer/2022062304/5681332a550346895d9a19f4/html5/thumbnails/34.jpg)
34
Another Example
Using Sells(store, candy, price) and a unary relation RipoffStores(store) created for the purpose, maintain a list of stores that raise the price of any candy by more than $1.
![Page 35: Constraints](https://reader035.vdocuments.us/reader035/viewer/2022062304/5681332a550346895d9a19f4/html5/thumbnails/35.jpg)
35
The Trigger
CREATE TRIGGER PriceTrigAFTER UPDATE OF price ON SellsREFERENCING
OLD ROW AS oooNEW ROW AS nnn
FOR EACH ROWWHEN(nnn.price > ooo.price + 1.00)INSERT INTO RipoffStores
VALUES(nnn.store);
The event –only changesto prices
Updates let ustalk about oldand new tuples
We need to considereach price change
Condition:a raise inprice > $1
When the price changeis great enough, addthe store to RipoffStores
![Page 36: Constraints](https://reader035.vdocuments.us/reader035/viewer/2022062304/5681332a550346895d9a19f4/html5/thumbnails/36.jpg)
36
Triggers on Views
Generally, it is impossible to modify a view, because it doesn’t exist.
But an INSTEAD OF trigger lets us interpret view modifications in a way that makes sense.
Example: We’ll design a view Synergy that has (consumer, candy, store) triples such that the store sells the candy, the consumer frequents the store and likes the candy.
![Page 37: Constraints](https://reader035.vdocuments.us/reader035/viewer/2022062304/5681332a550346895d9a19f4/html5/thumbnails/37.jpg)
37
Example: The View
CREATE VIEW Synergy ASSELECT Likes.consumer, Likes.candy, Sells.storeFROM Likes, Sells, FrequentsWHERE Likes.consumer = Frequents.consumer
AND Likes.candy = Sells.candyAND Sells.store = Frequents.store;
Natural join of Likes,Sells, and Frequents
Pick one copy ofeach attribute
![Page 38: Constraints](https://reader035.vdocuments.us/reader035/viewer/2022062304/5681332a550346895d9a19f4/html5/thumbnails/38.jpg)
38
Interpreting a View Insertion
We cannot insert into Synergy --- it is a view.
But we can use an INSTEAD OF trigger to turn a (consumer, candy, store) triple into three insertions of projected pairs, one for each of Likes, Sells, and Frequents. The Sells.price will have to be NULL.
![Page 39: Constraints](https://reader035.vdocuments.us/reader035/viewer/2022062304/5681332a550346895d9a19f4/html5/thumbnails/39.jpg)
39
The Trigger
CREATE TRIGGER ViewTrigINSTEAD OF INSERT ON SynergyREFERENCING NEW ROW AS nFOR EACH ROWBEGININSERT INTO LIKES VALUES(n.consumer, n.candy);INSERT INTO SELLS(store, candy) VALUES(n.store, n.candy);INSERT INTO FREQUENTS VALUES(n.consumer, n.store);END;