Understanding Database Constraints & Triggers: Keys, Foreign Keys, Constraints, & Triggers, Slides of Database Management Systems (DBMS)

Download Understanding Database Constraints & Triggers: Keys, Foreign Keys, Constraints, & Triggers and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Constraints

Foreign Keys

Local and Global Constraints

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.

Review: Single-Attribute Keys

• Place PRIMARY KEY or UNIQUE after the type in the declaration of the attribute.
• Example:

CREATE TABLE Beers ( name CHAR(20) UNIQUE, manf CHAR(20) );

Review: Multiattribute Key

• The bar and beer together are the key for Sells: CREATE TABLE Sells ( bar CHAR(20), beer VARCHAR(20), price REAL, PRIMARY KEY (bar, beer) );

Expressing Foreign Keys

• Use keyword REFERENCES, either:
  1. After an attribute (for one-attribute keys).
  2. As an element of the schema: FOREIGN KEY () REFERENCES ()
• Referenced attributes must be declared PRIMARY KEY or UNIQUE.

Example: With Attribute

CREATE TABLE Beers (

name CHAR(20) PRIMARY KEY, manf CHAR(20) );

CREATE TABLE Sells (

bar CHAR(20), beer CHAR(20) REFERENCES Beers(name), price REAL );

Enforcing Foreign-Key Constraints

• If there is a foreign-key constraint from relation R to relation S , two violations are possible:

1. An insert or update to R introduces values not found in S.
2. 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).

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.

Example: Setting Policy

CREATE TABLE Sells (

bar CHAR(20), beer CHAR(20), price REAL, 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.

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.