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 complex 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 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) ); 5 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) ); 6 Foreign Keys Values appearing in attributes of one relation must appear together in certain attributes of another relation. Example : in Sells(bar, beer, price) , we might expect that a beer value also appears in Beers.name . 7 Expressing Foreign Keys Use keyword REFERENCES, either: 1. After an attribute (for one-attribute keys). 2. As an element of the schema: FOREIGN KEY (<list of attributes>) REFERENCES <relation> (<attributes>) Referenced attributes must be declared PRIMARY KEY or UNIQUE. 8 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 ); 9 Example : As Schema 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)); 10 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 ....
View Full Document

Page1 / 37

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