1 of 6

Referential Integrity

2 of 6

Referential Integrity

With a relational database tables are linked together with relationships. This means that if you alter the contents or structure of a table (such as removing an entry or changing a column name) this can have an effect on other tables. If this happens then steps must be taken to maintain referential integrity.

​

​

3 of 6

Example - Deleted Tuple

Here we have 2 tables, where the ClassID is a foreign key in the pupil table.

​

Pupil Table

Pupil ID

First Name

Last Name

ClassID

1

Bob

Jones

1

2

Bill

Jones

2

3

Fred

Jones

1

ClassTable

Class ID

Class Name

Room

1

CompSci 101

S16

2

English 101

M42

4 of 6

Example - Deleted Tuple

Here we have 2 tables, where the ClassID is a foreign key in the pupil table.

If a class is cancelled then this will have an effect on all pupils whose class is the one that is cancelled.

​

Pupil Table

Pupil ID

First Name

Last Name

ClassID

1

Bob

Jones

1

2

Bill

Jones

2

3

Fred

Jones

1

ClassTable

Class ID

Class Name

Room

1

CompSci 101

S16

2

English 101

M42

5 of 6

Example - Deleted Tuple

Here we have 2 tables, where the ClassID is a foreign key in the pupil table.

If a class is cancelled then this will have an effect on all pupils whose class is the one that is cancelled.

They must be either be:

  • Assigned a Null value to ClassID (if allowed)

​

Pupil Table

Pupil ID

First Name

Last Name

ClassID

1

Bob

Jones

1

2

Bill

Jones

Null

3

Fred

Jones

1

ClassTable

Class ID

Class Name

Room

1

CompSci 101

S16

6 of 6

Example - Deleted Tuple

Here we have 2 tables, where the ClassID is a foreign key in the pupil table.

If a class is cancelled then this will have an effect on all pupils whose class is the one that is cancelled.

They must be either be:

  • Assigned a Null value to ClassID (if allowed)
  • or moved to another class

Pupil Table

Pupil ID

First Name

Last Name

ClassID

1

Bob

Jones

1

2

Bill

Jones

1

3

Fred

Jones

1

ClassTable

Class ID

Class Name

Room

1

CompSci 101

S16