Change all tuples of Sells that have beer Bud to have beer NULL Suppose we

Change all tuples of sells that have beer bud to have

This preview shows page 14 - 23 out of 28 pages.

– Change all tuples of Sells that have beer = ‘Bud’ to have beer = NULL. Suppose we update the Bud tuple by changing ‘Bud’ to ‘Budweiser’. – Same change.
Image of page 14
15 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.
Image of page 15
16 Example CREATE TABLE Sells ( bar CHAR(20), beer CHAR(20), price REAL, FOREIGN KEY(beer) REFERENCES Beers(name) ON DELETE SET NULL ON UPDATE CASCADE );
Image of page 16
17 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.
Image of page 17
18 Attribute-Based Checks Put a constraint on the value of a particular attribute. CHECK( <condition> ) must be added 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. Note: MySQL does not seem to support this – Accept definition, but does not enforce it Other DBMS, e.g., PostgreSQL, may support it
Image of page 18
19 Example CREATE TABLE Sells ( bar CHAR(20), beer CHAR(20) CHECK ( beer IN (SELECT name FROM Beers)), price REAL CHECK ( price <= 5.00 ) );
Image of page 19