6. Database integrity

By analysing what could go wrong, we can design a database system that guarantees some error patterns won’t happen.

Let’s look at the planets and moons database again. Let’s assume we have this entry:

MoonName: Deimos
PlanetName: Mars
Area: 495

What could go wrong?

PlanetName could be mistyped as Mers or set to NULL. This planet doesn’t exist, so our queries will produce erroneous results.
Area should never negative, so we should disallow negative values.

Some of these problems that can arise are detectable and preventable if we design the database for that.

Integrity Constraints can help us prevent errors. Here are some examples:

Join fields must match – We should use a FOREIGN KEY. A subsequent INSERT with wrong value will fail.
One some values of a field are valid – Use CHECK column constraint. eg Area INT CHECK (Area > 0) to prevent negative areas.
Tables values should not be inconsistent – Avoid repeating information in a database. We can do this by using the PRIMARY KEY which guarantees uniqueness.
Also, avoid storing calculated values. eg Rather than a field listing the number of moons, just do a COUNT.
Changes should not cause inconsistency – Use FOREIGN KEY rules to enforce correct behaviour.
eg FOREIGN KEY (PlanetName) REFERENCES Planets(PlanetName) ON DELETE CASCADE
When a parent entry in Planets is deleted, this will remove related moons.
Table values should not be inconsistent – Remove functional dependencies.