Integrity Constraints

A relationship among data elements that the DBMS is required to enforce

Types

• Keys
• Foreign key (referential-integrity)
• Value based
  • Constrain values of a particular attribute
• Tuple Based
  • Relationship among attributes
• Assertions
  • Any SQL boolean expression

Model

• Event
  • When do we need to check
• Condition
  • What do we need to check
• Action
  • Do we reject, cascade, set null, run arbitrary commands, etc. if the condition fails

Syntax

Attribute Based Checks

• Add CHECK (<constraint>) to after the attribute
• Can contain a query
• Performed only when a value for that attribute is inserted or updated
• Ex:
  • beer CHAR(20) CHECK (beer IN SELECT name FROM beers)
    • Different from foreign key constraint
    • The event is different (only triggers on modifications to that table, not the referenced table)
  • price REAL CHECK (price <= 5)

Tuple Based Checks

• CHECK (<condition>) added as a relation-schema element
• May refer to any attribute of the relation
• Event: Insert or update to the relation

CREATE TABLE Sells (  
	bar CHAR(20),  
	beer CHAR(20),  
	price REAL,  
	
	CHECK (bar = "Joe's Bar" OR price <= 5.00)  
);

Assertions

• These are database-schema elements, e.g., relations, views.
• Defined by: CREATE ASSERTION <name> CHECK ( <condition> );
• Condition may refer to any relation or attribute in the database schema.
• The DBMS often can’t tell when they need to be checked
  • Hurts performance because it will check when it doesn’t need to
  • Triggers can let the user decide when to check for a powerful condition

Triggers

• Specify an event, condition, and action

CREATE/REPLACE TRIGGER <name>
[AFTER/BEFORE/INSTEAD OF] [INSERT/UPDATE/DELETE] [OF <attr>](opt) ON relation
REFERENCING [NEW/OLD] [ROW/TABLE] AS <name>
FOR EACH [ROW/STATEMENT]
WHEN (<boolean condition>)
BEGIN
<ddl statement 1>
<ddl statement 2>
...
END;

• Event
  • AFTER or BEFORE (also INSTEAD OF if the relation is a view)
  • REFERENCING
    • INSERT statements imply a new tuple (for row-level) or new table (for statement-level)
      • The ‘table’ is the set of inserted tuples
    • DELETE implies an old tuple or table
    • UPDATE implies both
  • FOR EACH
    • Triggers are either ‘row-level’ or ‘statement-level’
    • Row level triggers: execute once for each modified tuple.
    • Statement-level triggers: execute once for an SQL statement.
• Condition
  • WHEN condition: boolean valued condition
• Action
  • BEGIN