Transcript ppt

Advanced SQL Concepts Checking of Constraints
CIS 4301
Lecture Notes
Lecture 21 - 4/6/2006
Check Constraints


Limits values that may appear in
components for some attributes
Expressed as either


Constraint on attribute in definition of its
relation’s schema
Constraint on a tuple as a whole; part of
relation schema, but not associated with
individual attributes
© CIS 4301 - Spring 2006
Lecture 21
2
Attribute Check Constraint
Simple Case:
 Assume in table Studio we have declaration:
presC# INT REFERENCES MovieExec(cert#) NOT NULL
Cannot use set-null policy to fix referential int. violation
General Case:
 Attached to attribute declaration
 Keyword CHECK followed by any condition that could
follow WHERE clause in SQL query



Checked whenever any tuple gets a new value for this attribute
(incl. on inserts of new tuples)
Not checked when modification does not change the value of
the attribute to which CHECK belongs
© CIS 4301 - Spring 2006
Lecture 21
3
Example
CREATE TABLE MovieStar (
…
gender CHAR(1) CHECK (gender IN (‘F’,
‘M’)),
…
);
Condition being checked can be anything
that could follow WHERE in SFW query
© CIS 4301 - Spring 2006
Lecture 21
4
Example

Can the following attribute-based CHECK constraint
simulate a referential integrity constraint?
CREATE TABLE Studio (
Name CHAR(30) PRIMARY KEY,
Address VARCHAR(255),
presC# INT CHECK
(presC# IN (SELECT cert# FROM MovieExec));

No, updates to MovieExec are invisible to the above
CHECK constraint
© CIS 4301 - Spring 2006
Lecture 21
5
Tuple-Based Check Constraint

Add a tuple-based CHECK constraint to MovieStar
schema that prevents the insertion of male stars whose
name begin with “Ms.”

Checked after insertions and updates to tuples of the relation
on which it is defined
CREATE TABLE MovieStar (
Name CHAR(30) PRIMARY KEY,
Address VARCHAR(255),
Gender CHAR(1),
Birthdate DATE,
CHECK (gender <> ‘M’ OR name NOT LIKE ‘Ms.%’)
//forbid the insertion of tuples that satisfy multiple conditions,
namely “male and name starts with ‘Ms.’ ”
//equivalent to the OR of the negation of the same terms
© CIS 4301 - Spring 2006
Lecture 21
6
Assertions

More powerful mechanism of constraining
values in database are part of database schema


First-class database citizens like views or relations
Assertion is a boolean-valued SQL expression
that must be true at all times


Easy to state for DB implementer, simply state what
must be true
Harder to implement efficiently since DBMS must
deduce whether or not a given database modification
could affect truth of assertion
© CIS 4301 - Spring 2006
Lecture 21
7
Example

Express that no one can become president of a
studio unless net worth greater than $10M
MovieExec(name, address, cert#, netWorth)
Studio(name, address, presC#)
CREATE ASSERTION RichPres CHECK
(NOT EXISTS
(SELECT *
FROM Studio, MovieExec
WHERE presC# = cert# AND netWorth < 10000000)
)
);

Can this be simulated with tuple-based CHECK
constraints?
© CIS 4301 - Spring 2006
Lecture 21
8
Using a Tuple-Based CHECK
CREATE TABLE Studio (
name CHAR(30) PRIMARY KEY,
address VARCHAR(255),
presC# INT REFERENCES MovieExec(cert#),
CHECK (presc# NOT IN
(SELECT cert#
FROM MovieExec
WHERE netWorth < 10000000)
)
);
© CIS 4301 - Spring 2006
Lecture 21
9
Example
Assert that the total length of all movies by a given studio
shall not exceed 10,000 minutes

Movie(title,year,length,inColor,studioName,producerC#)
CREATE ASSERTION SumLength CHECK
(10000 >= ALL
(SELECT SUM(length)FROM Movie
GROUP BY StudioName));

Is the effect the same as that of the following tuplebased CHECK:
CHECK (10000 >= ALL
(SELECT SUM(length) FROM Movie
GROUP BY studioName));
© CIS 4301 - Spring 2006
Lecture 21
10
Summary of CHECK Constraints
and Assertions
Type of
Constraint
When
Declared
When
Activated
Guaranteed to
Hold
Attribute-based
CHECK
With attribute
On insertion to
relation or
attribute update
Not if subqueries
Tuple-based CHECK
Element of
relational schema
On insertion to
relation or
tuple update
Assertion
Element of
Database schema
On any change to
any mentioned
relation
© CIS 4301 - Spring 2006
Lecture 21
Not if subqueries
Yes
11
Triggers


Aka “event-condition-action” (ECA) rules
Three important facts about triggers



Only awakened when certain events,
specified by db programmer, occur
Executing triggers involves testing a
condition first
If condition satisfied, action of trigger is
executed
© CIS 4301 - Spring 2006
Lecture 21
12
Example

Write trigger to prevent any attempt to lower
networth of movie exec
MovieExec(name,address,cert#,netWorth)
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#;
© CIS 4301 - Spring 2006
Lecture 21
Event
Condition
Action
13
Comments

The action rule may be executed BEFORE or AFTER the
event





If before, when clause is tested before triggering event
Besides update, other triggering events are insert and
delete
When clause is optional
The action may contain any number of SQL statements,
separated by BEGIN … END
If triggering event is insert, may use a NEW ROW AS
clause to give name to inserted row

Conversely, may use OLD ROW AS in case of a deletion
© CIS 4301 - Spring 2006
Lecture 21
14
More Comments


If we omit FOR EACH ROW clause, trigger
becomes statement-level trigger (as opposed to
row-level trigger)
Statement-level trigger is executed ONCE no
matter how many rows it actually effects


Cannot refer to old and new tuples
However, both types of triggers can access old
and new set of tuples


OLD TABLE AS … (i.e., deleted tuples or old versions
of updated tuples)
NEW TABLE AS … (i.e., inserted tuples or new
versions of updated tuples)
© CIS 4301 - Spring 2006
Lecture 21
15
Example


Prevent average net worth of movie executives to drop below $500K
Violation on insert, update, delete => need three triggers!
MovieExec(name,address,cert#,netWorth)
CREATE TRIGGER AvgNetWorthTrigger
AFTER UPDATE OF netWorth ON MovieExec
REFERENCING
OLD TABLE AS OldStuff,
NEW TABLE AS NewStuff
FOR EACH STATEMENT
WHEN (500000 > (SELECT AVG(netWorth) FROM MovieExec))
BEGIN
DELETE FROM MovieExec
WHERE (name,address,cert#,netWorth) IN NewStuff;
INSERT INTO MovieExec
(SELECT * FROM OldStuff);
END;
© CIS 4301 - Spring 2006
Lecture 21
16