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
Examples:
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
Classification
Non-null
Key
Referential integrity (foreign key)
Attribute-based
Tuple-based
General assertions
Declaration
with original schema – checked after bulk loading
or later – checked on current DB
Enforcement
check after every “dangerous” modification
deferred constraint checking – check after every transaction
Triggers – dynamic
monitor database changes, check conditions and initiate actions
“Event-Condition-Action-Rules”
When event occurs, check condition; if true, do action
Examples:
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)
action