What happens when a foreign key Constraint is violated ?(Cascade Example: o DeleteBud"Cascade deletes all Sells tuples that mention Bud. ●Update“Bud”→“Budweiser? change all Sells tuples with "Bud"in beer column to be "Budweiser
What happens when a foreign key Constraint is violated ? (Cascade ) Example: zDelete “Bud” Cascade deletes all Sells tuples that mention Bud. zUpdate “Bud” Æ “Budweiser” change all Sells tuples with “Bud”in beer column to be “Budweiser
What happens when a foreign key Constraint is violated (cont.) Set null:Change referring tuples to have null in referring components. Example: Delete“Bud.”Set-null makes all Sells tuples with "Bud"in the beer component have Null there. Update“Bud'→“Budweiser same change
What happens when a foreign key Constraint is violated ? (cont.) Set null: Change referring tuples to have null in referring components. Example: z Delete “Bud.” Set-null makes all Sells tuples with “Bud” in the beer component have Null there. z Update “Bud” Æ “ Budweiser” same change
Selecting a Policy:"Correct"policy is a design decision Add ON [DELETE,UPDATE] [CASCADE,SET NULL]to declaration of foreign key. Example CREATE TABLE Sells( bar CHAR (20),beer CHAR(20) price REAL, FOREIGN KEY beer REFERENCES Beers(name)ON DELETE SET NULLL ON UPDATE CASCADE); Otherwise,the default (reject)is used
Selecting a Policy: “Correct”policy is a design decision Add ON [DELETE, UPDATE] [CASCADE, SET NULL] to declaration of foreign key. Example CREATE TABLE Sells ( bar CHAR (20), beer CHAR (20), price REAL, FOREIGN KEY beer REFERENCES Beers (name) ON DELETE SET NULLL ON UPDATE CASCADE); Otherwise, the default (reject) is used
Attribute-based Checks Follow an attribute by a condition that must hold for that attribute in each tuple of its relation. Form:CHECK(condition) 1) Condition may involve the checked attribute. 2) Other attributes and relations may be involved,but only in subqueries. Condition is checked only when the associated attribute changes (i.e.,an insert or update occurs)
Attribute-based Checks Follow an attribute by a condition that must hold for that attribute in each tuple of its relation. z Form: CHECK (condition) 1) Condition may involve the checked attribute. 2) Other attributes and relations may be involved, but only in subqueries. z Condition is checked only when the associated attribute changes (i.e., an insert or update occurs)