Constraints and Triggers约束和触发器.pptVIP

Constraints and Triggers Constraints and Triggers Constraint: relationship among data elements DBMS should enforce the constraints Example: key constraints Triggers: Actions that are executed when a specified condition occurs Easier to implement than many constraint Example: insert a tuple Constraints Keys Foreign-key (referential integrity) Value-based constraints Tuple-based constraints Assertions (SQL boolean expression) Foreign Keys Requires that values for certain attributes must appear in other relations. Example: Dog(name, breed, Owner) the the value for the owner of the dog must appear in the Owner relation Foreign keys Keyword REFERENCES either Within the declaration of an attribute when only one attribute involved OR As an element of the schema Example: FOREIGN KEY (attributes) REFERENCES relation (attributes); Referenced attributes must be declared as PRIMARY KEY Example Dog(name,breed,owner) CREATE TABLE Owner ( name CHAR (20) PRIMARY KEY, SSN INT, phone CHAR (20)); CREATE TABLE Dog ( name CHAR(20) PRIMARY KEY , breed CHAR(10), owner CHAR(20) REFERENCES Owner(name) ); Enforcing Foreign-Key Constraints If there is a foreign-key constraints from relation R to the primary-key of another relation S, then possible violations: Insert or update on R may introduce values not in S Deletion or update on S may remove values needed for tuples in R Tuples of R without a matching primary-key in S are called dangling tuples Actions to prevent Foreign-Key Violations Insertions or updates that would create a dangling tuple must be REJECTED Example: If a new dog is inserted into the Dog relation before the owner’s data is inserted into the Owner relation Actions for Deleting/Modifying Tuples Needed for Foreign-Key Three possible ways to handle: Default: REJECT the modification Cascade: make the same changes in the referencing relation Set NULL: change the referencing attribute to NULL Example: Cascade Suppose owner ‘Alexandra Smith’ is deleted