2020-03-18 11:17
Page 1


Lecture 7: SQL Constraints and Triggers

Page 2

Database design continued

Page 3

Database system reliability

Page 4

Active element hierarchy

From the simples to the most powerful

Page 5

Live demo


Page 6

Active element hierarchy

Use appropriate column types

Page 7
Active element hierarchy

Use inline constraints to further restrict attribute values

Page 8
Active element hierarchy

Use constraints when more than one attribute is involved

Page 9

Pattern matching

Checking that strings follow a given format

Page 10

Referential constraints

Page 11

Cascading changes

Page 12


Not widely supported :-(

Page 13


Page 14

Function definitions

Page 15

Triggers on tables

Page 16

Triggers on views

Page 17

Defining functions for use in triggers

Page 18


In a trigger function executed for each row

Page 19

Local variables in functions in PL/pgSQL

Page 20

IF statements in PL/pgSQL

Page 21

Atomic transactions

All or nothing semantics

Page 22

One more example

Page 23

Further reading

Page 24

Next Time

Thursday 20/2: Alternative data models, JSON.