CS157A: Introduction to Database Management Systems
Chapter 7. Constraints and Triggers Suneuy Kim
1
Constraints and Triggers
• A constraint describe allowable database states.
Example: Key constraints, referential integrity constraints (also called foreign key constraints)
• A trigger checks conditions when database is changed (by insert, delete, update) and takes an action when it is triggered.
2
[Q] Who is going to check the correctness
of any update command ? Application or DBMS ?
[A] It’s better to save checks with database so that DBMS administer them.
[Because]
• Checks won’t be forgotten
• Can avoid duplication of work (modular)
3
Kinds of Constraints
1. Non-null
2. Keyconstraints
3. Referentialintegrityconstraints(foreignkey) 4. Attribute-basedconstraint
Constrain values of a particular attribute. 5. Tuple-based constraint
Relationship among components 6. General assertions
4
Non null constraint
CREATE TABLE USER
(uID INT,
uNAME VARCHAR(30), age INT not null, loaned INT, PRIMARY KEY (uID)
);
5
Key Constraints
CREATE TABLE USER (uID INT,
uNAME VARCHAR(30), age INT,
loaned INT,
PRIMARY KEY (uID)
);
6
Referential Integrity Constraints (Foreign key constraints)
• There should not be any dangling pointers • Referential integrity from R.A to S. B
– The attribute B must be the PRIMARY KEY or UNIQUE in relation S.
– Each value in column A of relation R must appear in column B of relation S.
• R.AàS.B does not mean S.BàR.A
7
Referential Integrity Constraints Declaration with Attributes
CREATE TABLE LOAN
(uID INT REFERENCES USER(uid),
title VARCHAR(50) REFERENCES Book(title), loanDate DATE DEFAULT ‘0000-00-00’, overdue BOOLEAN DEFAULT FALSE, PRIMARY KEY(uID,title,loanDate)
);
8
Referential Integrity Constraints Declaration as Schema Element
CREATE TABLE LOAN (uID INT,
title VARCHAR(50),
loanDate DATE DEFAULT ‘0000-00-00’, overdue BOOLEAN DEFAULT FALSE, PRIMARY KEY(uID,title,loanDate),
FOREIGN KEY(uID) REFERENCES User(uID), FOREIGN KEY(title) REFERENCES Book(title)
);
9
A foreign key consisting of multiple attributes
CREATE TABLE A (
aID INT PRIMARY KEY, x INT,
y VARCHAR(10)
);
CREATE TABLE B
(
bNum INT,
bName VARCHAR(10), PRIMARY KEY (bNUM, bNAME)
);
ALTER TABLE A ADD CONSTRAINT aREFb
FOREIGN KEY (x,y) REFERENCES B(bNUM, bName) on update cascade;
insert into B values (10, ‘apple’);
insert into A values (1, 20, ‘apple’); Foreign key constraint violation
insert into A values (1, 10, ‘apple’);
update B set bNum = 100 where bNUM = 10; The update is
cascaded to A.
10
CREATE TABLE A (
aID INT PRIMARY KEY,
fkey INT REFERENCES B(bID) );
CREATE TABLE B
( bID INT PRIMARY KEY );
Does not required to check if the existence of any value in the referenced column.
insert into A (aID) values (0);
will set the fkey of A to Null and this change avoids the constraint violation.
Null in a foreign key
11
Enforcing foreign key constraints
Consider Loan.titleàBook.title
Possible violation cases
• Case1:insertingaLoantupleofwhichtitleisnot null and is not the title of any Book tuple.
• Case2:updatingaLoantuplewithatitlewhichis not null and is not the title of any Book tuple.
• Case3:deletingaBooktupleofwhichnon-Null title appears as the title of a Loan tuple.
• Case 4: updating a Book tuple with a new title and the old title is the title of a Loan tuple.
12
Enforcing foreign key constraints
• Cases 1 and 2: Simply reject it !
• Cases 3 and 4: when a change in the parent relation affects a foreign key valueàIt is possible for DBMS to modify it in away that doesn’t violate the constraint
– The Default Policy : Reject violating modifications – The Cascade Policy: Make the same change in R.A
• Delete a Book with title ‘Bambi’àdelete Loans with title ‘Bambi’
• Update the title Bambi with Bambi II in a Book relation àupdate the titles of Loans whose title is ‘Bambi’ with ‘Bambi II’.
– The Set-Null Policy: Set the title of involved Loans to NULL
13
Choosing a Policy
• When we declare a foreign key, we may choose policies SET NULL or CASCADE independently for deletions and updates.
• Follow the foreign-key declaration by: ON [UPDATE, DELETE][SET NULL,CASCADE] • Otherwise, the default (reject) is used
14
CREATE TABLE LOAN (uID INT,
title VARCHAR(50),
loanDate DATE,
Example
overdue BOOLEAN DEFAULT FALSE,
PRIMARY KEY(uID,title,loanDate),
FOREIGN KEY(uid) REFERENCES user(uid) on delete cascade,
FOREIGN KEY(title) REFERENCES Book(title) on delete cascade
);
15
Circular Constraints (Postgres)
CREATE TABLE chicken
(cID INT PRIMARY KEY,
eID INT REFERENCES egg(eID));
CREATE TABLE egg
(eID INT PRIMARY KEY,
cID INT REFERENCES chicken(cID));
àError ! Why ?
16
Way around
CREATE TABLE chicken
(cID INT PRIMARY KEY,
eID INT);
CREATE TABLE egg
(eID INT PRIMARY KEY,
cID INT);
ALTER TABLE chicken ADD CONSTRAINT chickenREFegg
FOREIGN KEY (eID) REFERENCES egg(eID);
ALTER TABLE egg ADD CONSTRAINT eggREFchicken FOREIGN
KEY (cID) REFERENCES chicken(cID);
However, you can’t insert any tuple to these tables!
insert into chicken values (1,2); will fail!
insert into egg values (2,1); will fail!
17
Way around
insert into chicken values (1,null);
insert into egg values(2, null);
update chicken
set eID = 2
where cID =1;
update egg
set cID = 1
where eID = 2;
18
Deferred Constraints (Postgres) ALTER TABLE chicken ALTER CONSTRAINT
chickenREFegg DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE egg ALTER CONSTRAINT eggREFchicken DEFERRABLE INITIALLY DEFERRED;
19
Deferred Constraints
START TRANSACTION;
SET CONSTRAINTS ALL DEFERRED; INSERT INTO chicken VALUES(1, 2); INSERT INTO egg VALUES(2, 1); COMMIT TRANSACTION;
The foreign key constraints are declared as “deferred” and only checked at the commit point.
20
Deferred Constraint Options
• NOTDEFERRABLE:Theconstraintwillbechecked immediately after each statement.
• DEFERRABLEINITIALLYDEFERRED:Theconstraint check will be deferred until the commit point.
• DEFERRABLE INITIALLY IMMEDIATE: The constraint will be checked immediately after each statement
• You can change DEFERRED to IMMEDIATE and vice versa using SET CONSTRAINT command.
SET CONSTRAINT chickenREFegg DEFERRED;
21
Deferred Constraints (Postgres)
• To drop the tables with foreign key constraints, we have to drop the constraints first.
ALTER TABLE egg DROP CONSTRAINT eggREFchicken;
ALTER TABLE chicken CONSTRAINT chickenREFegg;
DROP TABLE egg;
DROP TABLE chicken;
22
MySQL doesn’t support deferred constraint checking.
23
Attribute-Based Checks
• Constraints on the value of a particular attribute.
• Add CHECK(condition) to the declaration for the attribute. The condition is anything that can appear in WHERE clause in SQL.
• Theconditionmayusethenameoftheattribute being constrained.
• If the condition refers to any other relations or attributes of other relations , the relation must be introduced in the FROM clause of a subquery.
• Checked if any tuple gets a new value for this attribute by insert or update.
24
Example
CREATE TABLE USER (uID INT,
uNAME VARCHAR(30), age INT CHECK (age >=10), loaned INT,
PRIMARY KEY (uID)
);
25
Example: Erroneous attempt to simulate foreign key constraint
CREATE TABLE LOAN
(uID INT,
title VARCHAR(50) CHECK (title IN(SELECT
title from Book) ),
loanDate DATE DEFAULT DATE ‘0000-00-00′,
overdue BOOLEAN DEFAULT FALSE,
PRIMARY KEY(uID,title,loanDate));
insert into LOAN values (123,’Web Server
Programming’,CURRENT_DATE(), false);
26
Timing of Checks
• Important: an attribute-based constraint is checked
only when the constrained attribute is updated. • Example: CHECK (age >= 10)
checks every new age and rejects the modification (for that tuple) if the age is less than 10.
• Example: CHECK (title IN (SELECT title from BOOK)) not checked if a title is deleted from Book
(erroneous attempt to simulate the foreign-key constraint).
27
Tuple-Based Checks
• CHECK (condition) may be added as a relation- schema element.
• The condition may refer to any attribute of the relation, but other relations or attributes of other relations require a subquery.
• Checked for the new or updated tuple.
• Use De Morgan’s law to find the condition that violates the check constraint.
28
Example
CREATE TABLE LOAN
(uID INT,
title VARCHAR(50),
loanDate DATE DEFAULT ‘0000-00-00’, overdue BOOLEAN DEFAULT FALSE, PRIMARY KEY(uID,title,loanDate), CHECK (uID <> 123 or title <>‘Bambi’)
);
29
Example: Subquery in Check
CREATE TABLE LOAN
(uID INT,
title VARCHAR(50),
loanDate DATE DEFAULT ‘0000-00-00’,
overdue BOOLEAN DEFAULT FALSE,
PRIMARY KEY(uID,title,loanDate),
CHECK (title IN (SELECT title from Book)));
Note: Although a change in Book causes the condition to be false, the check can’t inhibit the change.
30
Attribute-based vs. Tuple-based Constraints
• If more than one attributes are involved in a constraint, use tuple-based constraints.
• If one attribute is involved, use either tuple- or attribute-based constraint; The condition checked is the same, but tuple based constraint will be checked more frequently since it is checked whenever any attribute of the tuple is updated.
31
MySQL
• MySQL enforces check-constraints starting from the version 8.0.16.
32
Assertion
• Interrelation constraints
• Thesearedatabase-schemaelements,like relations or views.
• Defined by:
CREATE ASSERTION name CHECK (condition);
• Wenameitsothatwecandeletetheassertion by name.
• Condition may refer to any relation or attribute in the database schema.
• Theassertionmustbealwaystruefortheentire database.
33
Example: Assertion
CREATE ASSERTION ReferentialIntegrity
CHECK (not exists (select * from Loan where uID not in (select uID from User));
Note: It is very common to write a condition in a negative form and use not exists.
34
Example: Assertion
Suppose there cannot be more number of users than the total number of copies of books in the library.
CREATE ASSERTION FewUser CHECK (
(select count(*)from User) <=
(select sum(copies)from Book)
);
35
Timing of Assertion Checks
• In principle, we must check every assertion after every modification to any relation of the database.
• A clever system can observe that only certain changes could cause a given assertion to be violated.
– Example: Insertion to Book will not affect FewUser.
36
MySQL: Assertion
• No RDBMS implementation supports Assertion yet.
37
Triggers
"Event-Condition-Action Rules"- When event occurs, check condition, if true, take an action.
Event: data base modification, e.g., insert Condition: Any SQL boolean-valued expression. Action: Any SQL statements
38
Motivation: Triggers
• To move logic from application into DB
• To enforce integrity constraints beyond what constraint system supports – sometimes constraint system is limited. Triggers can be more expressive.
• Automatic constraint "repair" by specifying repair in the action part.
39
Triggers
CREATE TRIGGER name BEFORE|AFTER|INSTEAD OF events ON R [referencing-variables]
[FOR EACH ROW| FOR EACH STATEMENT] When (condition)
Action
40
Trigger Options
• [FOR EACH ROW]
The trigger is activated at row level for each tuple
affected by the event.
• [FOR EACH STATEMENT]
– The trigger is activated at statement level.
• Example:Supposeadeletestatementdeletes10 tuples.
– With for each row option, trigger is activated 10 times: one for each deleted tuple
– With for each statement, trigger is activated once for the delete statement.
41
Trigger Options • [REFERENCING variable ]
OLD ROW AS|NEW ROW AS|OLD TABLE AS|NEW TABLE AS var
• Depending on the event – Insert: only NEW
– Delete: only OLD
– Update: both OLD and NEW
42
Trigger Options
• Row-level variables (OLD ROW AS, NEW ROW AS) vs. Table-level variables (OLD TABLE AS, NEW TABLE AS)
– Old row in delete means specific deleted row
– Old table in delete means all deleted tuples, not
referring old state of data base
• If a trigger is FOR EACH ROW, both row-level and table-level variables are available.
• IF a trigger is FOR EACH STATEMENT, only table-level variables are available.
43
Example: Triggers
To fail any attempt to lower the net worth of a
movie executive.
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#;
44
Trigger Time: Before vs. After • After trigger is more common.
• In a BEFORE trigger, you can change the NEW value with SET newtuple.col_name = value .
(Not all SQL dialects support this. For example, a NEW variable is not updatable in SQLite.)
• Such a SET statement (on newtuple) has no effect in an AFTER trigger because the row change will have already occurred
• A column named with OLD is read only.
45
Example: Before SQL Trigger
CREATE TRIGGER FixYearTrigger
BEFORE INSERT ON Movies
REFERENCING
NEW ROW AS NewRow
NEW TABLE AS NewStuff
FOR EACH ROW
WHEN NewRow.year IS NULL
UPDATE NewStuff SET year = 1915;
NOTE: NewStuff is a relation consisting of only the new row being inserted. We need a relation to write update statement on
46
Example: Before SQL Trigger
CREATE TRIGGER TransactionBeforeTrigger BEFORE INSERT ON TransactionTable REFERENCING NEW AS new_row
FOR EACH ROW
BEGIN
DECLARE newmonth SMALLINT;
SET newmonth=MONTH(new_row.DateOfTransaction);
IF newmonth < 4 THEN SET new_row.FiscalQuarter=3;
ELSEIF newmonth < 7 THEN SET new_row.FiscalQuarter=4;
ELSEIF newmonth < 10 THEN SET new_row.FiscalQuarter=1;
ELSE SET new_row.FiscalQuarter=2;
END IF;
END
47
Before SQL Trigger
INSERT INTO
TransactionTable(DateOfTransaction)
VALUES(CURRENT DATE) ;
For the SQL insert statement above, the "FiscalQuarter" column is set to 1, if the current date is September 24, 2013.
48
SQLite Triggers
CREATE TRIGGER [IF NOT EXISTS] trigger_name [BEFORE|AFTER|INSTEAD OF] [INSERT|UPDATE|DELETE] ON table_name [WHEN condition]
BEGIN statements; END;
49
Example: SQLite Trigger
DROP TRIGGER IF EXISTS InsertTrigger;
CREATE TRIGGER InsertTrigger
AFTER INSERT ON User
FOR EACH ROW
WHEN NEW.age > 10 and NEW.age <= 50
BEGIN
insert into Loan values (New.uID, 'Bambi',
DATE(), false);
END;
50
Example: SQLite Trigger
DROP TRIGGER IF EXISTS DeleteCascadeTrigger;
CREATE TRIGGER DeleteCascadeTrigger
AFTER DELETE ON User
FOR EACH ROW
BEGIN
delete from Loan where uID =Old.uID;
END;
51
Example: SQLite Trigger
DROP TRIGGER IF EXISTS UpdateTrigger;
CREATE TRIGGER UpdateTrigger
AFTER UPDATE ON Book
FOR EACH ROW
BEGIN
UPDATE Loan SET title = NEW.title
WHERE title = OLD.title;
END;
52
Example: SQLite Trigger
CREATE TRIGGER validate_age_before_insert_user BEFORE INSERT ON user
BEGIN SELECT
CASE
WHEN NEW.age < 10 THEN
RAISE (ABORT,'Invalid age')
END;
END;
sqlite> insert into user values (5555, ‘Smith’, 9, 2);
Error: Invalid age
53
MySQL Triggers
CREATE TRIGGER trigger_name
BEFORE|AFTER INSERT|DELETE|UPDATE
ON table_name
FOR EACH ROW
BEGIN … END
54
MySQL Triggers
• A trigger only can be invoked by one event.
• A trigger is immediately activated when the event occurs.
• There cannot be multiple triggers for a given table that have the same trigger event and action time. For example, two BEFORE UPDATE triggers for a table are not allowed.
Notes:
55
MySQL Triggers
• To work around this, you can define a trigger that executes multiple statements by using the BEGIN … END compound statement.
56
Example: MySQL version
DROP TRIGGER IF EXISTS InsertTrigger;
delimiter //
CREATE TRIGGER InsertTrigger
AFTER INSERT ON User
FOR EACH ROW
BEGIN
IF NEW.age > 10 and NEW.age <= 50 THEN
insert into Loan values (New.uID, 'Bambi',
CURRENT_DATE(), false);
END IF; END;
//
delimiter ;
57
Example: MySQL version
DROP TRIGGER IF EXISTS DeleteCascadeTrigger;
delimiter //
CREATE TRIGGER DeleteCascadeTrigger
AFTER DELETE ON User
FOR EACH ROW
BEGIN
delete from Loan where uID =Old.uID;
END;//
delimiter ;
58
Example: MySQL version
DROP TRIGGER IF EXISTS UpdateTrigger;
delimiter //
CREATE TRIGGER UpdateTrigger
AFTER UPDATE ON Book
FOR EACH ROW
BEGIN
UPDATE Loan SET title = NEW.title
WHERE title = OLD.title;
END//
delimiter ;
59
Events, Timing, and NEW and OLD
BEFORE
AFTER
INSERT
NEW updatable OLD X
NEW read only OLD x
UPDATE
NEW updatable OLD read only
NEW read only OLD read only
DELETE
NEW x
OLD read only
NEW x
OLD read only
The behaviors of these variables vary in different SQL dialects.
60