What is referential integrity?

Referential integrity is a concept related to foreign keys. This concept says that the foreign key value in a target table must be the primary key of some record in the source table. When this rule is disregarded, then we have the case where referential integrity is violated.

Let us look at the terminology: Integrity comes from whole, complete, complete, correct. Referential comes from reference, indicate something or someone. Therefore, referential integrity is to indicate something or someone in an integral, complete, correct way.

For example, see these two tables:

Carros
Placa (PK) | Modelo | Proprietário (FK)
-----------+--------+------------------
ABC-1233   | Passat | 1
DEF-4566   | Fiesta | 2
UUV-7890   | Palio  | 1

Proprietários
ID (PK) | Nome
--------+------
1       | Pedro
2       | Maria

These tables have referential integrity, since cars that have owners with ID 1 can be found in the owners table as being Pedro. The owner's car with ID 2 can be found as being from Maria.

Now, imagine that we will insert an EJB-6520, Celta model and owner car with ID 3. There is no owner of ID 3. If the database allows this inclusion, a violation of referential integrity, since a reference to a nonexistent entity is being made. The same would happen if we wanted to change the owner of one of the cars by setting the owner ID to 3.

On the other hand, if we want to delete Maria from the database without deleting the DEF-4566 card car and do not change it, we will again have a breach of referential integrity because if the database allows this deletion is done, the referential integrity will be violated to the terms of a car that has its owner, a now non-existent entity.

Most existing modern relational databases enforce referential integrity when you try to insert, change, or delete entities in which foreign keys are involved. However, MySQL when used with MyISAM engine is a notable exception (it is advisable to use InnoDB in these cases). It is also possible that the tables were modeled without the foreign key having been explicitly modeled in the database and only implicit at the application level, so the database will not be able to verify the integrity in that case.

If an integrity violation occurs, your database will display inconsistent records that point to entities that do not exist, which tends to manifest in applications in the form of various types of problems.