Transcript CSCI 204 Introduction to Computer Science II

<bookstore>
<book category="COOKING">
<title lang="en">Everyday Italian</title>
<author>Giada De Laurentiis</author>
<year>2005</year>
<price>30.00</price>
</book>
<book category="FICTION">
<title lang="en">Harry Potter</title>
<author>J K. Rowling</author>
<year>2005</year>
<price>29.99</price>
</book>
<book category="WEB">
<title lang="en">Learning XML</title>
<author>Erik T. Ray</author>
<year>2003</year>
<price>39.95</price>
</book>
</bookstore>
XML
CSCI 305
Introduction to Database Systems
Constraints and Triggers in SQL
Professor Brian R. King
Bucknell University – Computer Science Dept.
• SQL provides a mechanism for active elements
– Execute under certain conditions
• Useful for maintaining integrity in your data
– Restriction of values
– Referential integrity
– Auto assignment of values
Constraints and Triggers
• Constraint
– A relationship among data elements that DBMS is
required to enforce
– Examples:
• key constraints (PRIMARY KEY)
• UNIQUE
• NOT NULL
• Triggers:
– Executed when a specified condition occurs, such as
an insertion of a tuple
– Often easier to implement than complex constraints
Kinds of Constraints
• Keys
• Foreign key
– Referential integrity
• Value-based constraints
– Constrain values of a particular attribute
• Tuple-based constraints
– Relationship among components within a tuple
• Assertions
– Any SQL boolean expression
– Global constraint
Keys
• We've seen these constraints
– In your schema, use PRIMARY KEY next to
attribute
– OR, use UNIQUE (allows NULL)
• CREATE TABLE Beers (
•
name CHAR(20) PRIMARY KEY
•
manf CHAR(20)
• );
Multiattribute Keys
• If you have multiple keys, must list them
separately as a schema element
• CREATE TABLE Sells (
•
bar
CHAR(20),
•
beer
VARCHAR(20),
•
price
FLOAT,
•
PRIMARY KEY(bar, beer)
• );
Foreign Keys
• Values appearing in attributes of one relation
must appear together in certain attributes of
another relation
• Example:
– Sells(bar,beer,price)
– Beers(name,manf)
• We might want to enforce the rule that any beers
entered in the Sells relation also appears in
Beers.name
– Beers.name  PRIMARY KEY
– Sells.beer  FOREIGN KEY that REFERENCES
Beers.name
Foreign Keys in SQL
• In schema, specify foreign key
– after an attribute (for one-attribute keys only)
with REFERENCES R(attr)
– As an element of the schema:
FOREIGN KEY (a1, a2, …)
REFERENCES R(b1, b2, …)
• Referenced attributes must be declared
PRIMARY KEY or UNIQUE
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 FLOAT
);
Example: As Schema Element
• CREATE TABLE Beers (
name CHAR(20),
manf CHAR(20),
PRIMARY KEY (name)
);
• CREATE TABLE Sells (
bar CHAR(20),
beer CHAR(20),
price
FLOAT,
FOREIGN KEY(beer)
REFERENCES Beers(name)
);
Foreign-Key Constraints
• Suppose R has a primary key, S has a foreign
key referring to R's primary
• Types of violations
– An insert or update to S introduces values not
found in R
– A deletion or update to R causes some tuples of S
to "dangle"
Maintaining referential integrity
• Example: suppose R = Beers, S = Sells
• An insert or update to Sells (with foreign key)
that introduces a nonexistent beer must be
rejected
• A deletion or update to Beers (with primary
key) that removes a beer value found in Sells
can be handled in one of three ways…
Referential Integrity (cont.)
1. Default:
– Reject the modification because it is being used
2. Cascade:
– Make the same change in Sells
•
•
Delete a beer? Then delete the Sells tuple
Update a beer? Then change the value in Sells
3. SET NULL
– Change the beer in Sells to NULL
Example: Cascade
If our behavior is set to CASCADE:
• Delete the Bud tuple from Beers:
– Then, delete all tuples from the Sells relations that
have beer = 'Bud'
• Update the Bud tuple by changing 'Bud' to
'Budweiser'
– Then, change all Sells tuples with beer = 'Bud' to
beer = 'Budweiser'
Example: Set NULL
If our behavior is set to SET NULL
• Delete the Bud tuple from Beers
– Change all tuples of Sells that have beer = 'Bud' to
have beer = NULL
• Update the Bud tuple by changing 'Bud' to
'Budweiser'
– Same change as for deletion
Choosing a Policy in SQL
• FOREIGN KEY (attr)
REFERENCES tbl(attr)
[ON DELETE
(CASCADE | SET NULL)]
[ON UPDATE
(CASCADE | SET NULL)]
Example
• CREATE TABLE Beers (
name CHAR(20),
manf CHAR(20),
PRIMARY KEY (name)
);
• CREATE TABLE Sells (
bar CHAR(20),
beer CHAR(20),
price FLOAT,
FOREIGN KEY(beer)
REFERENCES Beers(name)
ON DELETE SET NULL
ON UPDATE CASCADE
);
ALTER TABLE
• Reminder – you do not need to create an
entirely new table. You can alter an existing
one
• ALTER TABLE tbl_name
ADD PRIMARY KEY (col_name,..)
| ADD FOREIGN KEY (col_name,…)
REFERENCES …
| DROP PRIMARY KEY
| DROP FOREIGN KEY
MySQL and referential integrity
FROM MySQL Reference Manual:
• For storage engines other than InnoDB,
MySQL Server parses the FOREIGN KEY syntax
in CREATE TABLE statements, but does not use
or store it.
…MySQL…
• Do keep in mind that these benefits come at
the cost of additional overhead for the
database server to perform the necessary
checks. Additional checking by the server
affects performance, which for some
applications may be sufficiently undesirable as
to be avoided if possible. (Some major
commercial applications have coded the
foreign key logic at the application level for
this reason.)
…MySQL
• MySQL gives database developers the choice of
which approach to use. If you don't need foreign
keys and want to avoid the overhead associated
with enforcing referential integrity, you can
choose another storage engine instead, such as
MyISAM. (For example, the MyISAM storage
engine offers very fast performance for
applications that perform only INSERT and
SELECT operations.
• See
http://dev.mysql.com/doc/refman/5.0/en/ansidiff-foreign-keys.html
Exercise 7.1.1a
CREATE TABLE Movies (
title
CHAR(100),
year
INT,
length
INT,
genre
CHAR(10),
studioName CHAR(30),
producerC# INT,
PRIMARY KEY (title, year),
FOREIGN KEY (producerC#) REFERENCES
MovieExec(cert#)
)
Exercise 7.1.1b
CREATE TABLE Movies (
title
CHAR(100),
year
INT,
length
INT,
genre
CHAR(10),
studioName CHAR(30),
producerC# INT REFERENCES MovieExec(cert#)
ON DELETE SET NULL
ON UPDATE SET NULL,
PRIMARY KEY (title, year),
)
Exercise 7.1.1c
CREATE TABLE Movies (
title
CHAR(100),
year
INT,
length
INT,
genre
CHAR(10),
studioName CHAR(30),
producerC# INT REFERENCES MovieExec(cert#)
ON DELETE CASCADE
ON UPDATE CASCADE,
PRIMARY KEY (title, year),
)
Exercise 7.1.1d
CREATE TABLE StarsIn (
movieTitle CHAR(100) REFERENCES Movie(title),
movieYear
INT,
starName
CHAR(30),
PRIMARY KEY (movieTitle,movieYear,starName),
)
Attribute-Based Checks
• Constraints on a value of a particular attribute
• Example:
– NOT NULL
• Do NOT allow attributes with this qualifier to be NULL
• Example: If I do not want any price in
Sells(bar,beer,price) to be null:
– CREATE TABLE Sells (
bar CHAR(20),
beer CHAR(20) REFERENCES
Beers(name),
price FLOAT NOT NULL
);
• (MySQL supports this!)
CHECK constraints
• Add CHECK(condition) to the declaration
for the attribute
• The condition may freely use the name of the
attribute
– However, any other relation or attribute name
must be in a subquery
Example: Attribute-Based Check
• CREATE TABLE Sells (
bar
CHAR(20),
beer CHAR(20)
CHECK (beer IN
(SELECT name FROM Beers)),
price FLOAT
CHECK (price <= 5.00)
);
Timing of checks
• Attribute-based checks are performed only when
a value for that attribute is inserted or updated
• If CHECK fails, then the tuple is not inserted (or
updated)
• Note: CHECK is ONLY performed when update /
insert is made on this schema, even if CHECK
refers to another relation
– EXAMPLE: CHECK (beer IN (SELECT name FROM Beers)
is NOT checked if a beer is deleted from Beers
– Foreign Keys do this check
SET check
• You can restrict components to be only a
specific set of values
• BOOK:
– gender CHAR(1) CHECK (gender IN
('F','M')),
• MySQL:
– gender SET('F','M'),
• Both accomplish the same thing
multi-attribute CHECK
• Need a check that requires multiple
attributes?
• Add a CHECK (<condition>) as a schema
element
– Condition can refer to any attribute of the relation
– Other attributes require a subquery
• Like single-attribute check, checked on insert
or update only
Example: Tuple-based CHECK
• Only Joe's Bar can sell beer for more than $5
• CREATE TABLE Sells (
bar
CHAR(20),
beer CHAR(20),
price FLOAT,
CHECK (bar = 'Joe''s Bar' OR
price <= 5.00)
);
Exercise 7.2.1a
• year INT CHECK (year >= 1915)
• length INT CHECK (length >= 60 AND length <=
250)
• studioName
SET('Disney','Fox','MGM','Paramount')
Naming Constraints
• Book discusses using keyword CONSTRAINT in
a schema
• This is OPTIONAL, and only makes sense if you
want to name your constraints
HW3.SQL
• You can resubmit your HW3.SQL before
Thursday morning
– Why? EXCELLENT PREPARATION FOR EXAM!
– You will receive 50% of your lost credit back IFF
you also include, for EVERY SQL statement that
had the incorrect output, a clear explanation (as
/* */ comment) of what was wrong with your
query and why, and how you corrected it.
• i.e. Do NOT just copy my source!!!
– DUE: Thursday before exam! Submitted on SVN!
Material after Exam #1 begins here
Enforcing integrity on an entire database:
CREATE ASSERTION
CREATE TRIGGER
Review of Constraints
Our constraints thus far have been on:
• a single attribute of a tuple inserted or updated
– Add CHECK(condition) to the declaration for the
attribute
– Through an attr definition (e.g. SET, UNIQUE, NOT
NULL, PRIMARY KEY, REFERENCES, etc.)
• a complete tuple inserted or updated
– Add CHECK(condition) as a schema element
– (PRIMARY | FOREIGN) KEY as schema element
• Useful for ensuring that only specific tuples are
allowed into a table
Subqueries and CHECK constraints
• We learned that we could allow subqueries as part of the CHECK
condition
• Example: only allow beers in Sells(beer, bar, price) as long as the
beer is in Beers relation.
– beer CHAR(20)
CHECK (beer IN
(SELECT name FROM Beers))
• PROBLEM: what if a beer is removed from Beers?
• Manually make sure that you have cross checks everywhere?
HOW???
– CREATE TABLE Beers (
name CHAR(20)
CHECK (name IN (SELECT beer FROM Sells)
– Makes no sense!
• We saw one solution – FOREIGN KEY
– Not all DBMSs implement them
Attribute and tuple constraints
• Very limited capabilities
– For an UPDATE, single attribute CHECK not verified
if an attribute does not change
– If CHECK condition mentions other relation in a
subquery, and data in subquery changes, this does
not change the data that was already verified.
• If you want to constrain data across tables in a
database schema, attribute- and tuple-checks
are very limited
• SOLUTION: Assertions and Triggers
Assertions
• An assertion, by definition, is a statement that
must be true at all times
• An assertion in SQL is a boolean-valued SQL
expression that must be true at all times
• Defined by:
– CREATE ASSERTION assertName
CHECK cond;
– Condition may refer to any relation or attribute in the
entire schema
– Created as an element of the database schema
• At same level as CREATE TABLE
Example: Assertion
• A common approach:
– Specify a query that selects tuples that violate the desired
condition, and use this with NOT EXISTS
• Example:
– No bar may charge an average price of more than $5
• Schema:
– Sells(bar, beer, price);
• CREATE ASSERTION NoRipOffs
• CHECK
•
(NOT EXISTS
•
(SELECT bar FROM Sells
GROUP BY bar
HAVING 5.00 < AVG(price))
• Why is this better than a tuple-based
constraint?
– If a low priced beer is no longer sold, it is deleted,
and the average price is increased
– What if this deletion yields average price > 5?
• Our tuple-based constraints only checked on
INSERT and DELETE
Example: Assertion
• Any approach that yields a boolean condition is a valid
assertion
• Example:
– Let's make sure there are not more bars than there are
drinkers
• Schema:
– Drinkers(name, addr, phone)
– Bars(name, addr, license)
• CREATE ASSERTION MoreDrinkers
• CHECK (
•
(SELECT COUNT(*) FROM Bars) <=
(SELECT COUNT(*) FROM Drinkers)
);
Comparison of Constraints
Exercise 7.4.1a
•
•
•
•
•
•
•
•
•
•
•
CREATE ASSERTION CHECK
(NOT EXISTS
(SELECT maker
FROM Product NATURAL JOIN PC AS P
WHERE maker IN
(SELECT L.maker
FROM Product NATURAL JOIN Laptop AS L
GROUP BY L.maker
)
)
);
Assertions – not efficient!
• An assertion must be checked after every
database modification to any relation in the
database
– Extremely powerful, but extremely inefficient
– Optimize assertion to check only affected relations
and operations?
• The DBMS usually can't do this
• Attribute and tuple-based checks are checked at
known times (insert and update only), but not
that powerful
– Useful for ensuring only valid tuples get added, or
updates don't violate said constraints
MySQL
Does not support
CREATE ASSERTIONS
:-b
• A solution that addresses the inefficiency of
assertions:
Triggers
Triggers let you decide when to check for any
condition
Event-Condition-Action Rules
• Another name for a "trigger" is an ECA rule, or eventcondition-action rule
• Event:
– Typically a type of database modification
– Example: BEFORE INSERT ON Sells
• Condition:
– Any SQL boolean expression
• Action:
– Any SQL statements
• NOTE: In MySQL, the condition is set up in the Action
– Ex: DELETE FROM … WHERE cond
MySQL Triggers
• CREATE TRIGGER triggerName
(BEFORE | AFTER)
(INSERT | DELETE | UPDATE) ON
tblName
FOR EACH ROW
statement;
• Refer to attributes using aliases OLD and NEW
– OLD.attr refers to attr of existing row before
updating or deletion
– NEW.attr refers to attr of a new row to be inserted
OR existing row after updating
Example
• Schema:
– Beers(name, manf)
– Sells(bar, beer, price)
• Suppose we want to automatically insert a beer
to Beers if a tuple is added to Sells with an
unknown beer
• CREATE TRIGGER BeerTrig
• AFTER INSERT ON Sells
• FOR EACH ROW
•
INSERT INTO Beers(name)
VALUES NEW.beer;
Block of statements in trigger
• Body of trigger can be a compound statement
• Syntax:
• BEGIN
statement_list
END
• The statement_list is a list of SQL statements,
each terminated by a semicolon
– This is a problem when using the client program…
delimiter
• delimiter $$
• CREATE TRIGGER …
…
FOR EACH ROW
BEGIN
stmt_1;
…
stmt_n;
END$$
• delimiter ;
MySQL differences from book
• No REFERENCING clause
– New row is always NEW, old is always OLD
• No WHEN clause
– Standard SQL has a WHEN clause.
– Usually not necessary because the condition can
be included in WHERE clause for DELETE and
UPDATE
– For INSERT, this is not possible
– We can use an IF / THEN / END IF to get the same
effect
• Schema:
– Sells(bar, beer, price)
• Automatically maintain a list of bars called
RipOff(bar) that raise the price of any beer by
more than $1
•
•
•
•
•
CREATE TRIGGER PriceTrig
AFTER UDPATE ON Sells
FOR EACH ROW
BEGIN
IF (NEW.price – OLD.price > 1.00) THEN
INSERT INTO RipOff VALUES NEW.bar;
END IF;
• END;
Some good examples
• Schema:
– Student(id,name,class)
• Create a trigger that automatically places a backup of each entry
deleted into a backup table
• Schema:
– Student_Backup(id,name,class,deleteTime:TIME)
•
•
•
•
CREATE TRIGGER StudentDeleteTrig
BEFORE DELETE ON Student
FOR EACH ROW
BEGIN
INSERT INTO Student_Backup
VALUES(OLD.id,OLD.name,OLD.class,CURTIME());
END$$
• (For these examples, I'll assume using the prompt rather than a
script, and set END to trail with $$
Useful functions
• SELECT CURTIME();
– 11:34:25
• SELECT CURDATE();
– 2011-02-28
• SELECT LOCALTIME();
– 2011-02-28 11:34:25
• SELECT CURRENT_USER();
– brk009
• SELECT USER();
– [email protected]
Another example
• Schema
– Student(id,name,SAT:INT,placement:VARCHAR);
• Set a trigger to automatically assign placement based on SAT each time a
new record is about to be inserted
• CREATE TRIGGER StudentPlacementTrig
• BEFORE INSERT ON Student
• FOR EACH ROW
• BEGIN
•
IF NEW.SAT < 1200 THEN
•
SET NEW.placement = "REJECT";
•
ELSEIF NEW.SAT < 1400 THEN
•
SET NEW.placement = "PROVISIONAL ACCEPT";
•
ELSE
•
SET NEW.placement = "ACCEPT";
•
END IF;
• END$$
• Schema
– Student(id,name,class);
• For each insertion, add an entry of the user id to a log that perform the DB
modification
– StudentInsertLog(userID,description,time);
•
•
•
•
•
•
CREATE TRIGGER StuInsertLogTrigger
AFTER INSERT ON Student
FOR EACH ROW
BEGIN
INSERT INTO StudentInsertLog
VALUES (CURRENT_USER(),
CONCAT('Insert Student ', NEW.id,
' ', NEW.name, ' ', NEW.class),
CURTIME());
• END$$
Example
• http://www.roseindia.net/sql/trigger/index.sh
tml
• http://www.roseindia.net/mysql/mysql5/trigg
ers.shtml