cs411-06d-sql4

cs411-06d-sql4 - CS411 Database Systems 06d: SQL-4...

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

View Full Document Right Arrow Icon
1 CS411 Database Systems 06d: SQL-4 Constraints and Triggers
Background image of page 1

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

View Full DocumentRight Arrow Icon
2 Why Do We Learn This?
Background image of page 2
3 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.
Background image of page 3

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

View Full DocumentRight Arrow Icon
4 Kinds of Constraints •K e y s . • 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.
Background image of page 4
5 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 some key in Beers is called a foreign - key constraint.
Background image of page 5

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

View Full DocumentRight Arrow Icon
6 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.
Background image of page 6
7 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 );
Background image of page 7

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

View Full DocumentRight Arrow Icon
8 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));
Background image of page 8
9 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.”
Background image of page 9

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

View Full DocumentRight Arrow Icon
10 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.
Background image of page 10
11 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. Deleted beer: delete Sells tuple. Updated beer: change value in Sells. 3. Set NULL : Change the beer to NULL.
Background image of page 11

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

View Full DocumentRight Arrow Icon
12 Example: Cascade • Suppose we delete the Bud tuple from Beers. – Then delete all tuples from Sells that have beer =
Background image of page 12
Image of page 13
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 45

cs411-06d-sql4 - CS411 Database Systems 06d: SQL-4...

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

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