SQL: Constraints and TriggersKeys: Fundamental ConstraintKeys...Referential Integrity ConstraintsDeclaring FK ConstraintsHow to Maintain?Slide 7Slide 8Constraining Attribute ValuesConstraining Values with User Defined ‘Types’More Complex Constraints...Declaring AssertionsDifferent Constraint TypesGiving Names to ConstraintsAltering ConstraintsTriggersElements of Triggers (in SQL3)Example: Row Level TriggerStatement Level TriggerBad Things Can HappenSQL: Constraints and Triggers•Chapter 6 Ullman and Widom•Certain properties we’d like our database to hold•Modification of the database may break these properties•Build handlers into the database definitionKeys: Fundamental Constraint•In the CREATE TABLE statement, use:–PRIMARY KEY, UNIQUECREATE TABLE MovieStar (name CHAR(30) PRIMARY KEY,address VARCHAR(255),gender CHAR(1));•Or, list at end of CREATE TABLEPRIMARY KEY (name)Keys...•Can use the UNIQUE keyword in same way–…but for any number of attributes–foreign keys, which reference attributes of a second relation, only reference PRIMARY KEY•Indexing KeysCREATE UNIQUE INDEX YearIndex ON Movie(year)•Makes insertions easier to check for key constraintsReferential Integrity Constraints•2 rules for Foreign Keys:Movies(MovieName, year)ActedIn(ActorName, MovieName)1) Foreign Key must be a reference to a valid value in the referenced table.2) … must be a PRIMARY KEY in the referenced table.Declaring FK Constraints•FOREIGN KEY <attributes> REFERENCES <table> (<attributes>)CREATE TABLE ActedIn (Name CHAR(30) PRIMARY KEY,MovieName CHAR(30)REFERENCES Movies(MovieName));•Or, summarize at end of CREATE TABLEFOREIGN KEY MovieName REFERENCES Movies(MovieName)•MovieName must be a PRIMARY KEYHow to Maintain?•Given a change to DB, there are several possible violations:–Insert new tuple with bogus foreign key value–Update a tuple to a bogus foreign key value–Delete a tuple in the referenced table with the referenced foreign key value–Update a tuple in the referenced table that changes the referenced foreign key valueHow to Maintain?•Recall, ActedIn has FK MovieName...Movies(MovieName, year)(Fatal Attraction, 1987)ActedIn(ActorName, MovieName)(Michael Douglas, Fatal Attraction)insert: (Rick Moranis, Strange Brew)How to Maintain?•Policies for handling the change…–Reject the update (default)–Cascade (example: cascading deletes)–Set NULL•Can set update and delete actions independently in CREATE TABLEMovieName CHAR(30)REFERENCES Movies(MovieName))ON DELETE SET NULLON UPDATE CASCADEConstraining Attribute Values•Constrain invalid values–NOT NULL–gender CHAR(1) CHECK (gender IN (‘F’, ‘M’))–MovieName CHAR(30)CHECK (MovieName IN (SELECT MovieName FROM Movies))•Last one not the same as REFERENCE–The check is invisible to the Movies table!Constraining Values with User Defined ‘Types’ •Can define new domains to use as the attribute type...CREATE DOMAIN GenderDomain CHAR(1)CHECK (VALUE IN (‘F’, ‘M’));•Then update our attribute definition...gender GenderDomainMore Complex Constraints...•…Among several attributes in one table–Specify at the end of CREATE TABLECHECK (gender = ‘F’ OR name NOT LIKE ‘Ms.%’)Declaring Assertions•CREATE ASSERTION <name> CHECK (<condition>)CREATE ASSERTION RichPres CHECK(NOT EXISTS(SELECT * FROM Studio, MovieExec WHERE presC# = cert# AND netWorth < 10000000))Different Constraint TypesType Where Declared When activated Guaranteed to hold?Attribute with attribute on insertion not if CHECK or update subqueryTuple relation schema insertion or not if CHECK update to subquery relationAssertion database schema on change to Yes any relation mentionedGiving Names to ConstraintsWhy give names? In order to be able to alter constraints.Add the keyword CONSTRAINT and then a name:ssn CHAR(50) CONSTRAINT ssnIsKey PRIMARY KEYCREATE DOMAIN ssnDomain INT CONSTRAINT ninedigits CHECK (VALUE >= 100000000 AND VALUE <= 999999999CONSTRAINT rightage CHECK (age >= 0 OR status = “dead”)Altering ConstraintsALTER TABLE Product DROP CONSTRAINT positivePriceALTER TABLE Product ADD CONSTRAINT positivePrice CHECK (price >= 0)ALTER DOMAIN ssn ADD CONSTRAINT no-leading-1s CHECK (value >= 200000000)DROP ASSERTION assert1.TriggersEnable the database programmer to specify:• when to check a constraint,• what exactly to do.A trigger has 3 parts:• An event (e.g., update to an attribute)• A condition (e.g., a query to check)• An action (deletion, update, insertion)When the event happens, the system will check the constraint, and if satisfied, will perform the action.NOTE: triggers may cause cascading effects. Database vendors did not wait for standards with triggers!Elements of Triggers (in SQL3)• Timing of action execution: before, after or instead of triggering event• The action can refer to both the old and new state of the database.• Update events may specify a particular column or set of columns.• A condition is specified with a WHEN clause.• The action can be performed either for• once for every tuple, or• once for all the tuples that are changed by the database operation.Example: Row Level TriggerCREATE TRIGGER NoLowerPricesAFTER UPDATE OF price ON ProductREFERENCING OLD AS OldTuple NEW AS NewTupleWHEN (OldTuple.price > NewTuple.price) UPDATE Product SET price = OldTuple.price WHERE name = NewTuple.nameFOR EACH ROWStatement Level Trigger CREATE TRIGGER average-price-preserveINSTEAD OF UPDATE OF price ON ProductREFERENCING OLD_TABLE AS OldStuf NEW_TABLE AS NewStufWHEN (1000 < (SELECT AVG (price) FROM ((Product EXCEPT OldStuf) UNION NewStuf))DELETE FROM Product WHERE (name, price, company) IN OldStuf;INSERT INTO Product (SELECT * FROM NewStuf)Bad Things Can HappenCREATE TRIGGER Bad-triggerAFTER UPDATE OF price IN ProductREFERENCING OLD AS OldTuple NEW AS NewTupleWHEN (NewTuple.price > 50) UPDATE Product SET price =
View Full Document
Unlocking...