Transcript Referential Integrity checks, Triggers and Assertions

Referential Integrity checks,
Triggers and Assertions
Examples from Chapter 7 of
Database Systems: the Complete Book
Garcia-Molina, Ullman, & Widom
Movie Database
• Movie (title, year, length, inColor, studioName,
producerC#)
• StarsIn (movieTitle, movieYear, starName)
• MovieStar(name, address, gender, birthdate)
• MovieExec(name, address, cert#, netWorth)
• Studio(name, address, presC#)
Specifying Update/Delete Handling
• CREATE TABLE Studio (
name char(30) primary key,
address varchar(255),
presC# int references MovieExec(cert#)
on delete set null
on update cascade
• Deleting the corresponding MovieExec record
sets presC# to Null
• Updating the MovieExec record modifies presC#
Not-null constraint
• CREATE TABLE Studio (
name char(30) primary key,
address varchar(255),
presC# int references MovieExec(cert#) Not Null
on update cascade
• No longer possible to follow set-null policy on
deletes to MovieExec
Attribute-value constraint
• CREATE TABLE Studio (
name char(30) primary key,
address varchar(255),
presC# int references MovieExec(cert#)
Check (presC# >= 100000)
• Insertions or updates will fail if they violate the
check condition
Attribute-value constraint
• CREATE TABLE Studio (
name char(30) primary key,
address varchar(255),
presC# int references MovieExec(cert#)
Check (presC# in (Select cert# from MovieExec)
• Insertions or updates on this table will fail unless
the new presC# matches an existing MovieExec
• However, updates or deletes on MovieExec that
falsify the condition will not be stopped.
Tuple-based constraint
• CREATE TABLE MovieStar (
name char(30) primary key,
address varchar(255),
gender char(1),
birthdate date,
Check (gender=‘F’ or name NOT LIKE “Ms%”)
• Check condition is a relationship between two
different attributes
Assertion
• CREATE Assertion RichPres CHECK
(NOT EXISTS
(SELECT * FROM Studio, MovieExec
WHERE Studio.presC#=MovieExec.cert#
AND MovieExec.netWorth<10000000))
• Check condition requires an SQL statement
involving multiple tables
• Condition says that any president of a movie
studio must be worth at least $10,000,000
Another Assertion
• CREATE Assertion SumLength CHECK
(10000>=ALL
(SELECT SUM (length)
FROM Movie
GROUP BY StudionName))
• Assertion says that the lengths of all movies
made by any studio must be no more than
10,000 minutes
• Note the >= ALL quantifier!
Trigger
• CREATE Trigger NetWorthTrigger
AFTER UPDATE OF netWorth ON MovieExec
REFERENCING
OLD ROW AS OldTuple
NEW ROW AS NewTuple
FOR EACH ROW
WHEN (OldTuple.netWorth>NewTuple.netWorth)
UPDATE MovieExec
SET
netWorth = OldTuple.netWorth
WHERE
cert#=newTuple.cert#
• Prevents reducing the net worth of a movie exec
• Note this cannot be expressed as a constraint on tuple
values!