354.SQLTriggers

354.SQLTriggers - CMPT 354, Simon Fraser University, Fall...

Info iconThis preview shows pages 1–4. Sign up to view the full content.

View Full Document Right Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: CMPT 354, Simon Fraser University, Fall 2008, Martin Ester 157 Database Systems I SQL Constraints and Triggers CMPT 354, Simon Fraser University, Fall 2008, Martin Ester 158 Integrity Constraints An integrity constraint (IC) describes conditions that every legal instance of a relation must satisfy. Inserts/deletes/updates that violate ICs 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 ICs : domain constraints and NOT NULL constraints, primary key constraints and foreign key constraints, general constraints. CMPT 354, Simon Fraser University, Fall 2008, Martin Ester 159 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. CMPT 354, Simon Fraser University, Fall 2008, Martin Ester 160 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. CMPT 354, Simon Fraser University, Fall 2008, Martin Ester 161 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. CMPT 354, Simon Fraser University, Fall 2008, Martin Ester 162 Attribute-based CHECK Attribute-based CHECK constraint is part of an attribute definition....
View Full Document

Page1 / 10

354.SQLTriggers - CMPT 354, Simon Fraser University, Fall...

This preview shows document pages 1 - 4. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online