Megan Taylor

front-end dev, volunteacher, news & data junkie, bibliophile, Flyers fan, sci-fi geek and kitteh servant

Intro to Databases: Constraints and Triggers, Motivation and Overview

For relational databases
SQL standard; systems vary considerably

(Integrity) Constraints – static
constrain allowable database states
impose restriction on allowable data, beyond those imposed by structure and type

0.0 < GPA ≥ 4.0 enrollment < 50,000 -> 75,000
decision: ‘y’ ‘n’ NULL
major =’CS’ => decision = NULL
sizeHS < 200 => not admitted enrollment > 30,000

Why use them?
Data-entry errors (inserts)
correctness criteria (updates)
enforce consistency
tell system about data – store, query processing

Referential integrity (foreign key)
General assertions

with original schema – checked after bulk loading
or later – checked on current DB

check after every “dangerous” modification
deferred constraint checking – check after every transaction

Triggers – dynamic
monitor database changes, check conditions and initiate actions
When event occurs, check condition; if true, do action

enrollment > 35,000 => reject all applications
insert app with GPA > 3.95 => accept automatically
update sizeHS to be > 7,000 => change to “wrong” or raise error

Why use them?
Move logic from application into database system
to enforce constraints – expressiveness, “repair” logic

Create Trigger name
Before|After|Instead of events
[ referencing-variables ] [ For Each Row] When (condition)

November 28, 2011 | Comments Off on Intro to Databases: Constraints and Triggers, Motivation and Overview | Categories: Posts | Permalink

Comments are closed.