Chap 7 Constraints and Triggers Contents 2 u

Chap. 7 Constraints and Triggers

Contents 2 u Keys and Foreign Keys u Constraints on Attributes and Tuples u Modification of Constraints u Assertions u Triggers

Introduction · One way of assuring the correctness of new information – write application programs in such a way that they check the correctness for every insertion, deletion and update » these checks must be enforced in all the related programs /* Rule: The year of a movie must be at least 1860 */ New information from insert, delete or update could be wrong. Errors often occur in manually entered data. Read m. Title, m. Year part of application programs If(m. Year 1860) { INSERT INTO Movies(title, year) VALUES(: m. Title, : m. Year) } else print(“Incorrect year”) What if the rule is changed? What if a programmer does not know about the rules? Store checks as part of the DB schema. 3

Introduction (cont’d) without user’s explicit request u Active element » an expression or statement stored in database, and execute at appropriate times – Integrity Constraint (IC) » key constraint, foreign-key constraint » constraint on attributes, constraint on tuples » assertion – Trigger » called into play on a certain specified events, e. g. insert 4

Keys and Foreign Keys u Foreign-key Constraint foreign key constraint, referential integrity constraint – foreign key » an attribute(s) of one relation that references some key (i. e. , attributes) in another relation – requirement for the foreign key » the referenced attribute(s) must be declared as a key ü PRIMARY KEY (or UNIQUE) » any value of the foreign key in the referencing relation must also appear in the referenced attribute(s) of the referenced relation 5

Keys and Foreign Keys (cont’d) (Ex) Every studio president must be a movie executive. Studio(name, address, pres. C#) if 100 Movie. Exec(name, address, cert#, net. Worth) 150 reject if 150 is not in Movie. Exec. cert#! if 100 300 cascade update may be OK » Values of pres. C# in Studio tuples must be values of cert# in Movie. Exec tuples foreign-key referencing relation key referenced relation (Ex) STUDENT and DEPARTMENT STUDENT 6 dept-name d-name DEPARTMENT

Keys and Foreign Keys (cont’d) (Ex) Declaring a foreign-key CREATE TABLE Studio ( name CHAR(30) PRIMARY KEY, address VARCHAR(255), pres. C# INT REFERENCES Movie. Exec(cert#) ); CREATE TABLE Studio ( name CHAR(30) PRIMARY KEY, address VARCHAR(255), pres. C# INT, FOREIGN KEY pres. C# REFERENCES Movie. Exec(cert#) ); 7

Keys and Foreign Keys (cont’d) (example – cont’d) – referencing attribute (i. e. , foreign key): pres. C# in Studio – referenced attribute: cert# in Movie. Exec the key in Movie. Exec *Though a studio tuple may have a NULL in pres. C#, there is no requirement that NULL appears in cert# in Movie. Exec 8

Keys and Foreign Keys (cont’d) u check when: insert, update Maintaining Referential Integrity – changes to the referencing relation ü i. e. , the relation where the foreign-key constraint is declared » Reject violating modifications check when: delete, update e. g. , insert a studio whose president is not a movie executive – changes to the referenced relation » Reject violating modifications stated in the referencing relation 9 » Cascade policy e. g. , update a cert# » Set NULL policy e. g. , delete a movie executive who is a president of some studio,

Keys and Foreign Keys (cont’d) ¾ Default policy: Reject violating modifications – referencing relation » reject violating insertion or update – referenced relation » reject violating deletion or update 10

Keys and Foreign Keys (cont’d) ¾ Cascade policy actions in the referencing relation – Cascade deletion » if a referenced tuple is deleted, then delete referencing tuples – Cascade update » if a referenced tuple is updated, then update referencing tuples ¾ Set-Null policy » when a referenced tuple is deleted or updated, the foreign-key value in the referencing tuples is set to NULL 11

Keys and Foreign Keys (cont’d) (Ex) Maintaining referential integrity. [ ] : optional syntax { } : mandatory syntax < > : name of a syntactic element Studio(name, address, pres. C#) Movie. Exec(name, address, cert#, net. Worth) CREATE TABLE Studio ( name CHAR(30) PRIMARY KEY, address VARCHAR(255), pres. C# INT REFERENCES Movie. Exec(cert#) ON DELETE SET NULL ON UPDATE CASCADE ); If the referenced tuple is deleted or updated * [ON DELETE | ON UPDATE] 12 [CASCADE | SET NULL]

Keys and Foreign Keys (cont’d) This problem can be solved by first inserting into Move. Exec , and then inserting into Studio u Deferred Checking of Constraints – What if we want to insert a tuple that may first violate a foreign key constraint? (ex) Let Studio. pres. C# reference Movie. Exec. cert#. Suppose we want to insert a new Studio tuple whose president is “Bill Clinton”. Initially, Bill Clinton is not a movie executive – Insert Into Studio: “Bill Clinton is the president of Redlight studio” ü violate a foreign-key constraint, so will not be executed » This problem can be solved by first inserting a proper Bill Clinton tuple into Movie. Exec , and then inserting the above Studio tuple into the Studio table – Insert Into Movie. Exec; Insert Into Studio 13

Keys and Foreign Keys (cont’d) ¡ Circular constraints » two relations reference each other (ex) Consider the following two constraints in Studio and Movie. Exec circular constraints Studio. pres. C# references Movie. Exec. cert# references Studio. pres. C# » No tuples can be inserted into any of two tables violates the referential integrity all the time 14

Keys and Foreign Keys (cont’d) *A solution for the circular constraints » Group the two insertions into a single transaction START TRANSACTION “Insert into Studio” “Insert into Movie. Exec” COMMIT » Tell the DBMS not to check the constraints until after the whole transaction has finished its action and is about to commit 15 deferred checking of constraints

Keys and Foreign Keys (cont’d) ¾ Keywords about deferred checking of constraints – DEFERRABLE [INITIALLY DEFERRED | INITIALLY IMMEDIATE] indicates that we have the option of deferred checking of this constraint » INITIALLY DEFERRED checks are deferred to just before each transaction commits » INITIALLY IMMEDIATE checks are made immediately (i. e. , the check is not deferred) default – NOT DEFERRABLE cannot be changed to DEFERRED » for each dart modification, the constraint is checked immediately 16

Keys and Foreign Keys (cont’d) (Ex) Allow the checking of the foreign-key constraint to be deferred until the end of the transaction. CREATE TABLE Studio ( name CHAR(30) PRIMARY KEY, address VARCHAR(255), pres. C# INT REFERENCES Movie. Exec(cert#) DEFERRABLE INITIALLY DEFERRED ); checking time of deferrable constraints can be changed later on 17

Keys and Foreign Keys (cont’d) ¡ Change the checking time of a DEFERRABLE constraint IMMEDIATE DEFERRED – IMMEDIATE DEFERRED SET CONSTRAINT constraint-name DEFERRED – DEFERRED IMMEDIATE SET CONSTRAINT constraint-name IMMEDIATE * Note that constraints can have names 18

Constraints on Attributes and Tuples u Types of constraints – fundamental constraints on the relational database » primary key, referential integrity constraints – constraints on a single attribute » NOT NULL, UNIQUE constraints » attribute-based CHECK constraints within a CREATE TABLE statement – constraints on a tuple as a whole » tuple-based CHECK constraints – Assertions » general constraints 19 independent schema elements

Constraints on Attributes and Tuples (cont’d) u NOT NULL constraint – do not allow the value of an attribute to be NULL (ex) Relation Studio requires pres. C# not to be NULL CREATE TABLE Studio (. . . pres. C# INT REFERENCES Movie. Exec(cert#) NOT NULL ); » could not insert a tuple where pres. C# is NULL » could not update the value of pres. C# to be NULL » could not use the set-null policy to fix foreign-key violations 20

Constraints on Attributes and Tuples (cont’d) u Attribute-based CHECK constraints » constraints on values of the attribute, » attached to an attribute declaration – keyword CHECK, followed by a parenthesized condition » conditions can be anything that could appear in the WHERE clause – checked whenever any tuple gets a new value for this attribute ü by an update for the tuple, or by an insertion of a tuple » if the condition is not satisfied by the new value, the modification is rejected *Not checked when a tuple is deleted 21

Constraints on Attributes and Tuples (cont’d) (ex) pres. C# in Studio must be greater than 100000. CREATE TABLE Studio ( name CHAR(30) PRIMARY KEY, address VARCHAR(255), pres. C# INT REFERENCES Movie. Exec(cert#) CHECK (pres. C# >= 100000) ); (ex) gender in Movie. Star must be either ’F’ or ’M’. CREATE TABLE Movie. Star (. . . gender CHAR(1) CHECK (gender IN (‘F’, ‘M’)) ); 22

Constraints on Attributes and Tuples (cont’d) *Conditions involving other relations » a condition can refer to other relations by using a subquery ü the relations must be introduced in the FROM clause of a subquery » careful for cases when changes occur to the relations in the subquery CHECK constraints are checked only when the attribute to which the constraint is attached is modified 23

Constraints on Attributes and Tuples (cont’d) (Ex) Simulate a referential IC by a CHECK constraint. “pres. C# in Studio references cert# in Movie. Exec” CREATE TABLE Studio ( changes in Movie. Exec name CHAR(30) PRIMARY KEY, are not visible to this CHECK address VARCHAR(255), pres. C# INT CHECK (pres. C# IN (SELECT cert# FROM Movie. Exec))); * The effect is not exactly the same as the referential IC » when we modify Movie. Exec by deleting or updating a tuple, this change is not visible to the above CHECK constraint 24

Constraints on Attributes and Tuples (cont’d) *Time to activate an attribute-based CHECK constraints when a new tuple is inserted, or when a constraint-specified attribute is updated e. g. , Movie. Exec in the previous example » Thus, when a CHECK constraint involves other relations by a subquery, the CHECK constraint can be violated by changes to those relations mentioned in the subquery 25

Constraints on Attributes and Tuples (cont’d) u Tuple-based CHECK Constraints CHECK is specified, separately from attribute declarations – declare a constraint on the tuples of a single relation R » checking time, usage of condition, invisibility to changes of other relations are the same as in attribute-based CHECK constraints – checked every time a tuple of R is inserted or updated i. e. , condition is evaluated for the newly inserted or updated tuple When a condition mentions other relations in a subquery: - changes to tuples of those relations are not visible to the CHECK constraint - even a deletion from R can cause the condition to become false, if R is mentioned in a subquery. That is, the check does not inhibit this change CREATE TABLE Movies (. . . , CHECK(1000 < (SELECT SUM(length) FROM Movies WHERE genre=‘SF’)); 26

Constraints on Attributes and Tuples (cont’d) (Ex) If a star is male, his name must not begin with ‘Ms. ’ CREATE TABLE Movie. Star ( name CHAR(30) PRIMARY KEY, address VARCHAR(255), gender CHAR(1), birthdate DATE, CHECK (gender = ‘F’ OR name NOT LIKE ‘Ms. %’)); 27

Constraints on Attributes and Tuples (cont’d) u Comparison of Tuple- and Attribute-based constraints – if a constraint involves more than one attribute of the tuple » must be written as a tuple-based constraint – if a constraint involves only one attribute of the tuple » can be written as either a tuple-based or attribute-based constraint » the tuple-based constraint will be checked more frequently than the attribute-based constraint, because it is checked whenever any attribute of the tuple changes Attribute-based constraint: - checked only when the attribute mentioned in the constraint changes 28

Modifications of Constraints u Giving names to constraints – If we want to delete an existing constraint, it must have a name CONSTRAINT <name> <constraint> (ex) name CHAR(30) CONSTRAINT Name. Is. Key PRIMARY KEY (ex) gender CHAR(1) CONSTRAINT No. Andro CHECK(gender IN (‘F’, ‘M’)) (ex) CONSTRAINT Right. Title CHECK (gender = ‘F’ OR name NOT LIKE ‘MS. %’) 29 attribute-based CHECK tuple-based CHECK

Modifications of Constraints u Altering constraints on tables – ALTER TABLE statement used to add or drop attributes » can be used to add or drop constraints added constraints: must be of a kind that can be associated with tuples, e. g. , tuple-based CHECK constraints, key or foreign-key constraints ALTER TABLE <table name> [ADD|DROP] CONSTRAINT <name> *What if some existing tuple does not satisfy a newly added constraint? - a constraint can be added only when every tuple in the table satisfies the constraint at that time 30

Modifications of Constraints (cont’d) (ex) ALTER TABLE Movie. Star DROP CONSTRAINT Name. Is. Key (ex) ALTER TABLE Movie. Star DROP CONSTRAINT No. Andro (ex) ALTER TABLE Movie. Star DROP CONSTRAINT Right. Title * SET CONSTRAINT statement - switch between “IMMEDIATE” and “DEFERRED” 31 used to change the checking time of a constraint

Modifications of Constraints (cont’d) (ex) ALTER TABLE Movie. Star ADD CONSTRAINT Name. Is. Key PRIMARY KEY (name) (ex) ALTER TABLE Movie. Star ADD CONSTRAINT No. Andro CHECK(gender IN (‘F’, ‘M’)) (ex) ALTER TABLE Movie. Star ADD CONSTRAINT Right. Title CHECK(gender = ‘F’ OR name NOT LIKE ‘Ms. %’) * Modify an existing constraint - delete it first, and then add a new constraint 32 become tuple-based constraints

Assertions v Schema-level active elements » part of database schema, i. e. , equal level with tables – Assertion schema-level constraint » general constraint easy to use but hard to implement efficiently can enforce any condition that can be specified in WHERE » a boolean-valued SQL expression that must be true at all times – Trigger » a series of actions associated with certain events, and are performed whenever these events arise 33

Assertions (cont’d) u Creating an assertion: CREATE ASSSERTION CREATE ASSERTION <name> CHECK (<condition>) – the condition in an assertion must always be true attribute-based or tuple-based CHECK constraint can be violated, if they involve subqueries *Writing assertions » unlike attribute- or tuple-based CHECK statements, it may be difficult to directly refer to attributes for which conditions are defined » we may need a SELECT statement in the CHECK-condition in some way 34

Assertions (cont’d) u Using Assertions – common forms of <condition> the condition must have a boolean-value necessary to combine results to make a single true/false choice » NOT EXIST (subquery), EXIST (subquery) » SUM(column-i) > 500, MIN(column-j) >30, etc *Note: common forms of constraints » R = f, where R is a relation-producing expression 35 NOT EXIST (subquery) » value (Aggregated. Value) 100 (SELECT SUM(length) FROM R) » value quantifier (…) 100 ALL (SELECT length FROM R)

Assertions (cont’d) (Ex) No one can become the president of a studio unless their net worth is at least 10, 000 Studio (name, address, pres. C#) Movie. Exec (name, address, cert#, net. Worth) CREATE ASSERTION Rich. Pres CHECK (NOT EXISTS (SELECT pres. C# FROM Studio, Movie. Exec WHERE pres. C# = cert# AND net. Worth < 10000000 ) ); 36

Assertions (cont’d) can skip (ex-cont’d) A tuple-based CHECK constraint in the Studio schema. CREATE TABLE Studio ( name CHAR(30) PRIMARY KEY, address VARCHAR(255), pres. C# INT REFERENCES Movie. Exec (cert#), CHECK (pres. C# NOT IN (SELECT cert# FROM Movie. Exec WHERE networth < 10000000))); similar but not the same * Cannot catch a situation where the net worth of some studio president, as recorded in relation Movie. Exec, dropped below $10, 000 37

Assertions (cont’d) (Ex) The total length of all movies for each studio must not exceed 10, 000 minutes. Movies (title, year, length, gender, studio. Name, producer. C#) CREATE ASSERTION Sum. Length CHECK (10000 >= ALL (SELECT SUM(length) FROM Movies GROUP BY studio. Name); 38

Assertions (cont’d) (ex-cont’d) A tuple-based CHECK constraint in the Movies table. CREATE TABLE Movies ( title CHAR(100), . . . producer. C# INT, CHECK (10000 >= ALL (SELECT SUM(length) FROM Movies GROUP BY studio. Name) ); » the check would not be made on deletion of a tuple This does not cause violation of the constraint * If the constraint were “the total length must exceed 10, 000 minutes”, i. e. , “CHECK (10000 < ALL (SELECT. . . )”, the constraint can be violated 39

Note: When Assertions useful? v Useful cases of assertions – integrity rules that involve several relations » if tuple-based checks, changes of the relation mentioned in the subquery are not visible to the CHECK constraint – integrity rules affected by deletion of a tuple » if tuple-based checks, the check is not made on deletion of a tuple (ex) constraint on the aggregated value, e. g. , sum of a column 40

Assertions (cont’d) ¡ Drop an assertions DROP ASSERTION <assertion-name> 41

Assertions (cont’d) < Comparison of Constraints > Type of Constraint 42 When activated Guaranteed to hold? Attributed. With attribute based CHECK On insertion to relation or attribute update Not if subqueries Tuple. Element of based CHECK relation schema On insertion to relation or tuple update Not if subqueries Assertion On any change to any mentioned relation Yes Where declared Element of database schema

Triggers activated by user-specified events, and executes user-defined actions if user-specified condition is met u Trigger » an active element that is called into play on certain specified events e. g. , insertion into a specific relation – Event-Condition-Action rule ECA rule relatively easier for the DBMS because triggers tell exactly when the DBMS needs to deal with them (ex) If there is an attempt to lower the net worth of a movie executive, do not allow it. » Event: update the net worth of a movie executive » Condition: the net worth is lowered » Action: restore the net worth to what it was before the update 43

Triggers (cont’d) ¡ Differences of triggers from the integrity constraints – only awakened when certain events, specified by the DB programmer, occur » events : INSERT, DELETE, UPDATE, a transaction end, etc – once awakened by triggering event, check the conditions » if the condition does not hold, then no further action The trigger is fired or activated – if the condition is satisfied, the associated action is performed, one or more where possible actions: SQL statements » can be any sequence of database operations, in general modifies the effects of the event in some way aborts the triggering transaction, etc 44

Triggers (cont’d) u Creating a trigger: CREATE TRIGGER Insert, Delete, Update CREATE TRIGGER <trigger name> {BEFORE | AFTER} <triggering event> ON <subject table name> [ REFERENCING <OLD ROW or NEW ROW aliases > ] [ <Action granularity> ] [ WHEN (<condition>) ] BEGIN <Action > /* when more than one SQL statements */ /* atomically executed */ END There are many variations in commercial systems 45

Triggers (cont’d) u Principle features of triggers in SQL – the checking time of condition and action » can be executed before or after the triggering event – update events can be limited to a particular attribute(s) – condition and action can refer to old and new values of tuples – option of specifying that the trigger executes: Action granularity 46 » once for each modified tuple (a row-level trigger), or » once for all the tuples changed in one SQL statement (a statement-level trigger)

Triggers (cont’d) (Ex) Foil any attempt to lower the net worth of a movie executive. CREATE TRIGGER Net. Worth. Trigger AFTER UPDATE OF net. Worth ON Movie. Exec /* event*/ REFERENCING OLD ROW AS Old. Tuple, NEW ROW AS New. Tuple FOR EACH ROW row-level trigger WHEN(Old. Tuple. net. Worth > New. Tuple. net. Worth) UPDATE may not be allowed in some DBMSs, i. e. , it should not be a subject-table 47 SET WHERE /* action */ Movie. Exec net. Worth cert# = Old. Tuple. net. Worth = /* condition */ New. Tuple. cert#

Triggers (cont’d) u The Options for Trigger design l Condition and Action time – BEFORE » the condition test and action are executed before the triggering event – AFTER » the condition test and action are executed after the triggering event l Triggering event – {INSERT | DELETE | UPDATE} [OF attributes] ON <subject table name> » OF-attribute clause can be used with UDPATE, but cannot with INSERT and DELETE 48

Triggers (cont’d) l OLD ROW AS and NEW ROW AS clause » can be specified for a update event in a row-level trigger – give a name to the tuple before the update and to the tuple after the update » if the event is insertion, OLD ROW AS is not allowed » if the event is deletion, NEW ROW AS is not allowed l WHEN <condition> clause – if omitted, action is executed whenever an event occurs l Action executed atomically – can be several SQL statements surrounded by BEGIN. . . END 49

Triggers (cont’d) l Action granularity indicates on what unit the action operates » the default is the statement-level tuple-level trigger – row-level trigger: FOR EACH ROW » executed once for each modified tuple – statement-level trigger: FOR EACH STATEMENT » executed once for all the modifications made by one SQL statement » cannot refer to old tuple and new tuple directly *However, any trigger (whether row- or statement-level) can refer to the relations of old tuples and new tuples by using OLD TABLE AS and NEW TABLE AS 50 not supported in Oracle

Triggers (cont’d) (Ex) Prevent the average net worth of movie executives from dropping below $500, 000. Movie. Exec(name, address, cert#, net. Worth) – We need to write one trigger for each of three events » insert, delete and update of relation Movie. Exec ü i. e. , three triggers are needed * Actions for INSERT, DELETE, UPDATE are different 51

Triggers (cont’d) (Ex-cont’d) CREATE TRIGGER Avg. Net. Worth. Trigger AFTER UPDATE OF net. Worth ON Movie. Exec REFERENCING OLD TABLE AS Old. Stuff, NEW TABLE AS New. Stuff FOR EACH STATEMENT WHEN(500000 > (SELECT AVG(net. Worth) FROM Movie. Exec) BEGIN DELETE FROM Movie. Exec WHERE (name, address, cert#, net. Worth) IN New. Stuff; INSERT INTO Movie. Exec (SELECT * FROM Old. Stuff); END; 52

Triggers (cont’d) ¡ A useful case of BEFORE triggers » fix up the inserted tuples in some way before they are inserted (Ex) We want to insert tuples into Movies table. » sometimes we will not know the year of the movie ü NULL for year is not allowed, because year is part of the primary key use a default value if the value of year is NULL 53

Triggers (cont’d) (Ex-cont’d) CREATE TRIGGER Fix. Year. Trigger BEFORE INSERT ON Movies REFERENCING NEW ROW AS New. Row, NEW TABLE AS New. Stuff FOR EACH ROW WHEN(New. Row. year IS NULL) UPDATE New. Stuff SET year = 1915; 54

Note: INSTEAD OF trigger u Instead-Of Trigger » not in the SQL standard, but is supported by some commercial systems » use INSTEAD OF in place of BEFORE or AFTER – INSTEAD OF » when an event awakens a trigger, the action of the trigger is executed instead of the event itself the triggering event is not executed instead, the action of the trigger is executed *This capability offers little when trigger is on a stored table, but » is useful when used on a view 55