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