Referential Integrity checks, Triggers and Assertions

1
Referential Integrity checks, Triggers and
Assertions

• Examples from Chapter 7 of
• Database Systems the Complete Book
• Garcia-Molina, Ullman, Widom

2
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)

3
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

4
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

5
Attribute-value constraint

• CREATE TABLE Studio (
• name char(30) primary key,
• address varchar(255),
• presC int references MovieExec(cert)
• Check (presC gt 100000)
• Insertions or updates will fail if they violate
  the check condition

6
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.

7
Tuple-based constraint

• CREATE TABLE MovieStar (
• name char(30) primary key,
• address varchar(255),
• gender char(1),
• birthdate date,
• Check (genderF or name NOT LIKE
  Ms)
• Check condition is a relationship between two
  different attributes

8
Assertion

• CREATE Assertion RichPres CHECK
• (NOT EXISTS
• (SELECT FROM Studio, MovieExec
• WHERE Studio.presCMovieExec.cert
• AND MovieExec.netWorthlt10000000)
  )
• 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

9
Another Assertion

• CREATE Assertion SumLength CHECK
• (10000gtALL
• (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 gt ALL quantifier!

10
Trigger

• CREATE Trigger NetWorthTrigger
• AFTER UPDATE OF netWorth ON MovieExec
• REFERENCING
• OLD ROW AS OldTuple
• NEW ROW AS NewTuple
• FOR EACH ROW
• WHEN (OldTuple.netWorthgtNewTuple.netWorth)
• UPDATE MovieExec
• SET netWorth
  OldTuple.netWorth
• WHERE certnewTuple.cert
• Prevents reducing the net worth of a movie exec
• Note this cannot be expressed as a constraint on
  tuple values!