{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

354.SQLConstraints

354.SQLConstraints - DatabaseSystemsI...

Info icon This 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
Image of page 1

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

View Full Document Right 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.
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.
Image of page 3

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

View Full Document Right 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.
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.
Image of page 5

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

View Full Document Right 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.
Image of page 6
Image of page 7
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern