SQL: Constraints and Triggers

1
SQL Constraints and Triggers

• Chapter 6 Ullman and Widom
• Certain properties wed like our database to hold
• Modification of the database may break these
  properties
• Build handlers into the database definition

2
Keys Fundamental Constraint

• In the CREATE TABLE statement, use
• PRIMARY KEY, UNIQUE
• CREATE TABLE MovieStar (
• name CHAR(30) PRIMARY KEY,
• address VARCHAR(255),
• gender CHAR(1))
• Or, list at end of CREATE TABLE
• PRIMARY KEY (name)

3
Keys...

• Can use the UNIQUE keyword in same way
• but for any number of attributes
• foreign keys, which reference attributes of a
  second relation, only reference PRIMARY KEY
• Indexing Keys
• CREATE UNIQUE INDEX YearIndex ON Movie(year)
• Makes insertions easier to check for key
  constraints

4
Referential Integrity Constraints

• 2 rules for Foreign Keys
• Movies(MovieName, year)
• ActedIn(ActorName, MovieName)
• 1) Foreign Key must be a reference to a valid
  value in the referenced table.
• 2) must be a PRIMARY KEY in the referenced
  table.

5
Declaring FK Constraints

• FOREIGN KEY ltattributesgt REFERENCES lttablegt
  (ltattributesgt)
• CREATE TABLE ActedIn (
• Name CHAR(30) PRIMARY KEY,
• MovieName CHAR(30)
• REFERENCES Movies(MovieName))
• Or, summarize at end of CREATE TABLE
• FOREIGN KEY MovieName REFERENCES
  Movies(MovieName)
• MovieName must be a PRIMARY KEY

6
How to Maintain?

• Given a change to DB, there are several possible
  violations
• Insert new tuple with bogus foreign key value
• Update a tuple to a bogus foreign key value
• Delete a tuple in the referenced table with the
  referenced foreign key value
• Update a tuple in the referenced table that
  changes the referenced foreign key value

7
How to Maintain?

• Recall, ActedIn has FK MovieName...
• Movies(MovieName, year)
• (Fatal Attraction, 1987)
• ActedIn(ActorName, MovieName)
• (Michael Douglas, Fatal Attraction)
• insert (Rick Moranis, Strange Brew)

8
How to Maintain?

• Policies for handling the change
• Reject the update (default)
• Cascade (example cascading deletes)
• Set NULL
• Can set update and delete actions independently
  in CREATE TABLE
• MovieName CHAR(30)
• REFERENCES Movies(MovieName))
• ON DELETE SET NULL
• ON UPDATE CASCADE

9
Constraining Attribute Values

• Constrain invalid values
• NOT NULL
• gender CHAR(1)
• CHECK (gender IN (F, M))
• MovieName CHAR(30)
• CHECK (MovieName IN
• (SELECT MovieName FROM Movies))
• Last one not the same as REFERENCE
• The check is invisible to the Movies table!

10
Constraining Values with User Defined Types

• Can define new domains to use as the attribute
  type...
• CREATE DOMAIN GenderDomain CHAR(1)
• CHECK (VALUE IN (F, M))
• Then update our attribute definition...
• gender GenderDomain

11
More Complex Constraints...

• Among several attributes in one table
• Specify at the end of CREATE TABLE
• CHECK (gender F OR name NOT LIKE Ms.)

12
Declaring Assertions

• CREATE ASSERTION ltnamegt CHECK (ltconditiongt)
• CREATE ASSERTION RichPres CHECK
• (NOT EXISTS
• (SELECT
• FROM Studio, MovieExec
• WHERE presC cert
• AND netWorth lt 10000000))

13
Different Constraint Types
Type Where Declared When
activated Guaranteed

to hold? Attribute with
attribute on insertion not if
CHECK
or update subquery Tuple
relation schema insertion or not if
CHECK
update to subquery

relation Assertion database schema
on change to Yes
any relation

mentioned
14
Giving Names to Constraints
Why give names? In order to be able to alter
constraints. Add the keyword CONSTRAINT and then
a name ssn CHAR(50) CONSTRAINT ssnIsKey
PRIMARY KEY CREATE DOMAIN ssnDomain INT
CONSTRAINT ninedigits CHECK (VALUE gt
100000000
AND VALUE lt 999999999 CONSTRAINT
rightage CHECK (age gt 0 OR status
dead)
15
Altering Constraints
ALTER TABLE Product DROP CONSTRAINT
positivePrice ALTER TABLE Product ADD
CONSTRAINT positivePrice CHECK (price gt
0) ALTER DOMAIN ssn ADD CONSTRAINT
no-leading-1s CHECK (value gt
200000000) DROP ASSERTION assert1.
16
Triggers

• Enable the database programmer to specify
• when to check a constraint,
• what exactly to do.
• A trigger has 3 parts
• An event (e.g., update to an attribute)
• A condition (e.g., a query to check)
• An action (deletion, update, insertion)
• When the event happens, the system will check the
  constraint, and
• if satisfied, will perform the action.
• NOTE triggers may cause cascading effects.
• Database vendors did not wait for standards with
  triggers!

17
Elements of Triggers (in SQL3)

• Timing of action execution before, after or
  instead of triggering
• event
• The action can refer to both the old and new
  state of the database.
• Update events may specify a particular column or
  set of columns.
• A condition is specified with a WHEN clause.
• The action can be performed either for
• once for every tuple, or
• once for all the tuples that are changed by the
  database operation.

18
Example Row Level Trigger
CREATE TRIGGER NoLowerPrices AFTER UPDATE OF
price ON Product REFERENCING OLD AS
OldTuple NEW AS NewTuple WHEN
(OldTuple.price gt NewTuple.price) UPDATE
Product SET price OldTuple.price
WHERE name NewTuple.name FOR EACH ROW
19
Statement Level Trigger
CREATE TRIGGER average-price-preserve INSTEAD OF
UPDATE OF price ON Product REFERENCING
OLD_TABLE AS OldStuff NEW_TABLE AS
NewStuff WHEN (1000 lt (SELECT AVG
(price) FROM ((Product EXCEPT
OldStuff) UNION NewStuff)) DELETE FROM Product
WHERE (name, price, company) IN
OldStuff INSERT INTO Product (SELECT FROM
NewStuff)
20
Bad Things Can Happen
CREATE TRIGGER Bad-trigger AFTER UPDATE OF
price IN Product REFERENCING OLD AS OldTuple
NEW AS NewTuple WHEN
(NewTuple.price gt 50) UPDATE Product
SET price NewTuple.price 2
WHERE name NewTuple.name FOR EACH ROW