410 likes | 595 Views
Chapter 7 Constraints and Triggers . Spring 2011 Instructor: Hassan Khosravi. SQL: Constraints and Triggers. Certain properties we’d like our database to hold Modification of the database may break these properties Data entry may have errors Build handlers into the database definition.
E N D
Chapter 7 Constraints and Triggers Spring 2011 Instructor: Hassan Khosravi
SQL: Constraints and Triggers • Certain properties we’d like our database to hold • Modification of the database may break these properties • Data entry may have errors • Build handlers into the database definition
SQL: Constraints and Triggers • (integrity) constraints (static) • Constrain allowable database states • Triggers (Dynamic) • Monitor database changes • Check conditions and initiate conditions
(integrity) Constraints • Impose restrictions on allowable data beyond those imposed by structure and types • Examples on university database • 0 < gpa < 4.0 • Enrollment < 50,000 • Decision attribute: ‘y’ or ‘n’ • Major = ‘CS’ decision = null • sizeHS < 200 not addmittedenr > 5000 • Why use integrity constraints • Data-entry error (insert) • gpa in range • Correctness criteria (update) • Enforce consistency • Referenced tuples • Tell system about your data
Classification of Integrity Constraints • Non- null • Keys • Uniqueness • Referential integrities (foreign key) • Attribute-based • Constraining values in attributes • Tuples-based • How values in different tuples should correlate • General assertions
Declaration and enforcing Constraints • Declaration • With original schema when tables are declared • Once you have a running database • Enforcement • Check after every dangerous modification • Changing major we don’t need to check the gpa constraint • Deferred constraint checking • We may do some modifications that would raise errors • But after we have done all the modifications it should be ok • Check once some modifications are done (transaction)
Triggers • Event-Condition-Action rules • When event occurs, check condition, if true, then do action • Example • Enrolllment > 75000 reject all applications • If application with gpa > 3.95 accept automatically • Update sizehs to be > 7000 change to wrong and raise error • Why use triggers? • Move codes from application to DBMS • Enforce constraints • Some of the assertions and checks are not implemented in some DBMS • Triggers could not only detect the problem, they can also solve it
Non-null constraints • Defining that a specific attribute in a specific table can not take the value of null • Create table A(A1int, A2 int not null, A3 text) • Examples
Key Constraints • The primary key of the tables has to be unique • Create table A (A1int primary key, A2 int, A3 text) • Create table A (A1int, A2 int, A3 text) primary key A1, A2)) • You can also define other combination of attributes to be unique ( without declaring them as key) • Create table A (A1int primary key, A2 int, A3 text unique) • Create table A (A1int primary key, A2 int, A3 text , unique(A1, A2)) • Examples
Attribute base check constraints • Constraints on the attributes to have specific ranges or types • Create table A (A1int, A2 int, A3 text, check (A2 >0)) • Examples
Assertions • Assertions are very strong they are checks done over all tables at the same time. (assertions are generally not implemented in current dbms) • Create assertion name check () • Example
Referential integrity • Referential integrity is integrity of references. • No dangling pointers • What would referential integrity from S.sid to A.sid mean?
Referential Integrity • Examples
Triggers • Event-Condition-Action rules • When event occurs, check condition, if true, then do action • Example • Enrolllment > 75000 reject all applications • If application with gpa > 3.95 accept automatically • Update sizehs to be > 7000 change to wrong and raise error
Tricky issues with Triggers • Row-Level vs. Statement-level • Which one do you use? • Multiple triggers activated at the same time • For example a delete command triggers multiple triggers • Which one do you run first? • Chaining trigger action • Self triggering • Cycles Trigger A Trigger B Trigger C Trigger A • Nested triggers • Implementations are significantly different in different systems • Tricky example
Trigger Examples • For each student with GPA between 3.3 and 3.6, make them apply to geology in Stanford and biology in MIT
Trigger Examples • Implement referential integrity using triggers. • Delete all applications of a student from apply table after the student is deleted in the student table
Trigger Examples • Implement referential integrity using triggers on cascade. • Update the name of all colleges in the apply table if the name of the college is updated in the college table
Trigger Examples • Implement primary key using triggers. • Before inserting into table college check if the college exists. Raise an error if you get a duplicate • Example
Trigger Examples • Once a college has received 10 applications, add “-Done” to the end of the name of the college • Example of chains in triggers
Trigger Examples • If the size of the sizehk <100 or sizehk> 5000, then assume that its an error and ignore • Example considering before and after insert in chaining
Trigger Examples • Automatically accept students applying to Berkeley that have GPA > 3.7 and are coming from a highschool > 1200
Trigger Examples • Once a college passes a thresh-hold of 16000 applicants, delete new EE applicant and set all the decisions for all majors to undecided
More Trigger Examples (self triggers) • By default this may get triggered only once. • Recursive_trigger = on
More Trigger Examples (cycles) Example
More Trigger Examples (Conflicts) • What happens when you have multiple triggers being triggered at the same time
More Trigger Examples (Nested Triggers) • What order are triggers carried out in you have nested triggers • behaves like imperative programming