– 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.
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.
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 );
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.
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
19 Example CREATE TABLE Sells ( bar CHAR(20), beer CHAR(20) CHECK ( beer IN (SELECT name FROM Beers)), price REAL CHECK ( price <= 5.00 ) );