354.SQLConstraints

354.SQLConstraints - DatabaseSystemsI...

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

View Full Document Right Arrow Icon
CMPT 354, Simon Fraser University, Fall 2008, Martin Ester  1 Database Systems I  SQL Constraints and Triggers
Background image of page 1

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

View Full DocumentRight Arrow Icon
CMPT 354, Simon Fraser University, Fall 2008, Martin Ester  2 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.
Background image of page 2
CMPT 354, Simon Fraser University, Fall 2008, Martin Ester  3 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.
Background image of page 3

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

View Full DocumentRight Arrow Icon
CMPT 354, Simon Fraser University, Fall 2008, Martin Ester  4 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.
Background image of page 4
CMPT 354, Simon Fraser University, Fall 2008, Martin Ester  5 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.
Background image of page 5

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

View Full DocumentRight Arrow Icon
CMPT 354, Simon Fraser University, Fall 2008, Martin Ester  6 Attribute-based CHECK Attribute-based  CHECK  constraint is part of an  attribute definition. Is checked whenever a tuple gets a new value for 
Background image of page 6
Image of page 7
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 10/07/2009 for the course CS 12601 taught by Professor Kewang during the Spring '09 term at Simon Fraser.

Page1 / 19

354.SQLConstraints - DatabaseSystemsI...

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

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