Integrity constraints impose restrictions on the allowable data in the database, in addition to the simple structure and type restrictions imposed by the basic schema definition.
Question: We've already seen a few kinds of integrity constraints. What are they?
1. 2. 3.
Why use integrity constraints?
Example schema:
Student(ID, name, address, GPA, sizeHS) Campus(location, enrollment, rank) Apply(ID, location, date, major, decision)Example constraints:
CREATE TABLE Student (ID integer NOT NULL, name char(30) NOT NULL, address char(100), GPA float NOT NULL, sizeHS integer)
There are two kinds of keys in SQL:
CREATE TABLE Student (ID integer PRIMARY KEY, name char(30), address char(100), GPA float, sizeHS integer, UNIQUE (name,address))
Question: Does the reverse direction in these examples make sense?
Terminology:
CREATE TABLE Apply(ID integer REFERENCES Student(ID), location char(25), date char(10), major char(10), decision char, FOREIGN KEY (location) REFERENCES Campus(location))Can omit referenced attribute name if it's the same as referencing attribute:
ID integer REFERENCES Student, ...Can have multi-attribute referential integrity.
Can have referential integrity within a single table.
Example:
Dorm(first-name, last-name, room-number, phone-number, roommate-first-name, roommate-last-name, PRIMARY KEY (first-name,last-name), FOREIGN KEY (roommate-first-name,roommate-last-name) REFERENCES Dorm(first-name,last-name))
Question: What modification operations can violate constraint?
1. 2. 3. 4.Inserts/updates to S that violate constraint are disallowed.
(figure)
Four options:
See textbook for syntax.
Question: Why is referenced attribute required to be PRIMARY KEY or UNIQUE?
CREATE TABLE Student (... GPA float CHECK(GPA <= 4.0 AND GPA > 0), ...) CREATE TABLE Apply (... decision char CHECK(decision in ('Y','N','U')) ...)Conditions can be more complex, e.g., with subqueries.
Apply (... major char(10) CHECK(major NOT IN (SELECT dept-name FROM Dept WHERE status = 'full')) ...)Question: Can we encode referential integrity constraints this way?
-> Attribute-based constraints are only checked on inserts and updates to that relation, NOT on modifications to relations referenced in subqueries -- significant "hole" in constraint-checking.
Similar to attribute-based constraints except CHECK
applies to entire tuples.
=> Specified separately in table definition
Example:
CREATE TABLE Campus(location char(25), enrollment integer, rank integer, CHECK(enrollment >= 10,000 OR rank > 5)(Example encodes enrollment < 10,000 => rank > 5)
As with attribute-based constraints, can have subqueries but
constraint is only checked when the relation itself is modified
In SQL, stand-alone statement:
CREATE ASSERTION <name> CHECK(<condition>)Example: Average GPA is > 3.0 and average sizeHS is < 1000
CREATE ASSERTION Avgs CHECK( 3.0 < (SELECT avg(GPA) FROM Student) AND 1000 > (SELECT avg(sizeHS) FROM Student))Example: A student with GPA < 3.0 can only apply to campuses with rank > 4.
CREATE ASSERTION RestrictApps CHECK( NOT EXISTS (SELECT * FROM Student, Apply, Campus WHERE Student.ID = Apply.ID AND Apply.location = Campus.location AND Student.GPA < 3.0 AND Campus.rank <= 4))Assertions checked for each modification that could potentially violate them.
Question: What operations could violate assertion Avgs?
Question: What operations could violate assertion RestrictApps?
Q: Can we encode referential integrity constraints this way?
CREATE TRIGGER <name> BEFORE | AFTER | INSTEAD OF <events> <referencing clause> // optional FOR EACH ROW // optional WHEN (<condition>) // optional <action><events> can be:
INSERT ON R DELETE ON R UPDATE [OF A1, A2, ..., An] ON RAFTER <events> are the most common and useful. Don't worry too much about BEFORE and INSTEAD OF, they can be messy.
Example: If an application tuple is inserted for a student with GPA > 3.9 and sizeHS > 1500 to Berkeley, set decision to "Y".
CREATE TRIGGER AutoAccept AFTER INSERT ON Apply REFERENCING NEW ROW AS NewApp FOR EACH ROW WHEN (NewApp.location = 'Berkeley' AND 3.9 < (SELECT GPA FROM Student WHERE ID = NewApp.ID) AND 1500 < (SELECT sizeHS FROM Student WHERE ID = NewApp.ID)) UPDATE Apply SET decision = 'Y' WHERE ID = NewApp.ID AND location = NewApp.location AND date = NewApp.dateQuestion: Can we achieve the same effect using assertions?
Example: Same trigger without FOR EACH ROW
CREATE TRIGGER AutoAccept AFTER INSERT ON Apply REFERENCING NEW TABLE AS NewApps UPDATE Apply SET decision = 'Y' WHERE (ID,location,date) IN (SELECT ID,location,date FROM NewApps) AND location = 'Berkeley' AND 3.9 < (SELECT GPA FROM Student WHERE ID = Apply.ID) AND 1500 > (SELECT sizeHS FROM Student WHERE ID = Apply.ID)Example: If campus enrollment increases from below 7,000 to above 7,000, delete all applications to that campus dated after 2/15/04 and set all "Y" decisions for applications before 2/15/04 to "U".
CREATE TRIGGER TooMany AFTER UPDATE OF enrollment ON Campus REFERENCING OLD ROW AS OldVal NEW ROW AS NewVal FOR EACH ROW WHEN (OldVal.enrollment <= 7,000 AND NewVal.enrollment > 7,000) DELETE FROM Apply WHERE location = NewVal.location AND date > 2/15/04; UPDATE Apply SET decision = 'U' WHERE location = NewVal.location AND decision = 'Y'Question: Can we achieve the same effect using assertions?
Triggers and assertions:
Question: What is being prevented by restricting triggering interactions?