Referential integrity

In this previous section we have seen that foreign keys are used to maintain relationships between tables. You will have also noticed that by default SQLite does not enforce foreign keys and therefore we must explicitly enable this features using the PRAGMA foreign_keys = ON statement.

By telling SQLite to check for foreign keys you are enforcing referential integrity i.e. ensuring that the relationships between tables are appropriate. This means that if a values appears as a foreign key in one table, it must also appear as the primary key of a record in the referenced table.

If you did not enforce referential integrity several unwanted situations could arise:

  • If you deleted a product type which had been assigned to various products those products will be referring to a product type that isn't in the database
  • If you updated the ProductTypeID then any products that were referring to the previous id would not be pointing to the correct product type
  • If you changed the ProductTypeID for the product it may point to a product type that doesn't exist

All of these situations are prevented by turning on the enforcement of foreign keys in SQLite by using the PRAGMA foreign_keys = ON statement.

The video below demonstrates these issues:


Task 13

Watch the above video to gain a better understanding of the different issues that referential integrity addresses.


Making changes to records that involve foreign keys

Sometimes you will want to make changes to records that involve foreign keys, for instance maybe a vet has moved away so they need to be deleted from the database. At the moment the foreign key constraints prevent that from happening. You could switch these off but then when you delete the product type you have products that are referring to a non-existent product type! Thankfully, there are SQL clauses we can use to deal with this kind of situation:

  • ON UPDATE
  • ON DELETE

These clauses are added when you create an entity that contains a foreign key. By default each clause is added in the following way:

  • ON UPDATE RESTRICT - prevents the foreign key from being changed to a different value if it is being referenced by any other record
  • ON DELETE RESTRICT - prevents the foreign key from being deleted if it is being referenced by any other record

There are two other options that are of interest to us:

  • CASCADE - this can be used in place of RESTRICT and means that if your delete or update a foreign key any records that are linked to it are also deleted or updated
  • SET NULL - this can be used in place of RESTRICT and means that if your delete a foreign key any records associated with it will have their foreign key value set to NULL.

The video below demonstrates the differences between the available options:


Task 14

Watch the above video to gain a better understanding of how the cascade and set null options function.


Sticking with defaults

As a beginner the default option of ON UPDATE RESTRICT ON DELETE RESTRICT is generally the most appropriate option. This prevents you from mistakenly altering your database in a (possibly) unexpected way.