cons - 1 Constraints Foreign Keys Local and Global...

Info iconThis preview shows pages 1–11. Sign up to view the full content.

View Full Document Right Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: 1 Constraints Foreign Keys Local and Global Constraints Triggers 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 many constraints. 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. 4 Foreign Keys Consider Relation Sells(bar, beer, price). We might expect that a beer value is a real beer --- something appearing in Beers.name . A constraint that requires a beer in Sells to be a beer in Beers is called a foreign -key constraint. 5 Expressing Foreign Keys Use the keyword REFERENCES, either: 1. Within the declaration of an attribute, when only one attribute is involved. 2. As an element of the schema, as: FOREIGN KEY ( <list of attributes> ) REFERENCES <relation> ( <attributes> ) Referenced attributes must be declared PRIMARY KEY or UNIQUE. 6 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 ); 7 Example: As Element CREATE TABLE Beers ( name CHAR(20) PRIMARY KEY, manf CHAR(20) ); CREATE TABLE Sells ( bar CHAR(20), beer CHAR(20), price REAL, FOREIGN KEY(beer) REFERENCES Beers(name)); 8 Enforcing Foreign-Key Constraints If there is a foreign-key constraint from attributes of relation R to the primary key of 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. 9 Actions Taken -- 1 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. 10 Actions Taken -- 2 The three possible ways to handle beers that suddenly cease to exist are: 1. Default : Reject the modification. 2. Cascade : Make the same changes in Sells....
View Full Document

This note was uploaded on 08/01/2008 for the course CSC 540 taught by Professor Chirkova during the Spring '08 term at N.C. State.

Page1 / 40

cons - 1 Constraints Foreign Keys Local and Global...

This preview shows document pages 1 - 11. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online