2020-03-18 11:17
Page 1

Databases

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

Files

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

Assertions

Not widely supported :-(

Page 13

Triggers

Page 14

Function definitions

Page 15

Triggers on tables

Page 16

Triggers on views

Page 17

Defining functions for use in triggers

Page 18

NEW and OLD

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.