Database Systems I SQL Constraints and Triggers.ppt

Integrity Constraints An integrity constraint (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 and NOT NULL constraints, primary key constraints and foreign key constraints, general constraints. Not-Null Constraints The IC NOT NULL disallows NULL values for a specified attribute. CREATE TABLE Students (sid CHAR(20) PRIMARY KEY, name CHAR(20) NOT NULL, login CHAR(10) NOT NULL, age INTEGER, gpa REAL); What about specifying gpa as NOT NULL? Primary key attributes are implicitly NOT NULL. General Constraints Attribute-based CHECK defined in the declaration of an attribute, activated on insertion to the corresponding table or update of attribute. Tuple-based CHECK defined in the declaration of a table, activated on insertion to the corresponding table or update of tuple. Assertion defined independently from any table, activated on any modification of any table mentioned in the assertion. General Constraints Can use general SQL queries to express constraints. Much more powerful than domain and key constraints. Constraints can be named. Test of constraints can be deferred until the end of the corresponding transaction of the activating SQL statement. Attribute-based CHECK Attribute-based CHECK constraint is part of an attribute definition. Is checked whenever a tuple gets a new value for that attribute (INSERT or UPDATE). Violating modifications are rejected. CHECK constraint can contain an SQL query referencing other attributes (of the same or other tables), if their relations are mentioned in the FROM clause. CHECK constraint is not activated if other attributes mentioned get new values. Attribute-based CHECK Attribute-based CHECK constraints are most often used to restrict allowable att