《Insertsdeletesupdates that violate IC#39;s are disallo》-公开·课件设计.pptVIP

SQL: The Query LanguagePart 3 CS 186, Spring 2006 Lecture 13 R G - Chapter 5 Sorting the Results of a Query ORDER BY column [ ASC | DESC] [, ...] Views Discretionary Access Control Two more important topics Constraints SQL embedded in other languages Integrity Constraints (Review) An IC describes conditions that every legal instance of a relation must satisfy. Inserts/deletes/updates that violate IC’s are disallowed. Can be used to ensure application semantics (e.g., sid is a key), or prevent inconsistencies (e.g., sname has to be a string, age must be 200) Types of IC’s: Domain constraints, primary key constraints, foreign key constraints, general constraints. Domain constraints: Field values must be of right type. Always enforced. Primary key and foreign key constraints: you know them. General Constraints Useful when more general ICs than keys are involved. Can use queries to express constraint. Checked on insert or update. Constraints can be named. Constraints Over Multiple Relations Awkward and wrong! Only checks sailors! Only required to hold if the associated table is non-empty. ASSERTION is the right solution; not associated with either table. Unfortunately, not supported in many DBMS. Triggers are another solution. Or, Use a Trigger Trigger: procedure that starts automatically if specified changes occur to the DBMS Three parts: Event (activates the trigger) Condition (tests whether the triggers should run) Action (what happens if the trigger runs) Triggers (in some form) are supported by most DBMSs; Assertions are not. Support for triggers is defined in the SQL:1999 standard. Triggers Cannot be called directly – initiated by events on the database. Can be synchronous or asynchronous with respect to the transaction that causes it to be fired. Triggers: Example CREATE TRIGGER member_delete ON member FOR DELETE AS IF (Select COUNT (*) FROM loan INNER JOIN deleted ON loan.member_no = deleted.member_no) 0 BEGIN