What is referential integrity?

Question

I have searched several sites and so far I could not understand very well what is referential integrity. So I decided to ask here.

When to use referential integrity?

Answer 1

Referential integrity is a concept related to foreign keys. This concept says that the value that is a foreign key 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’s look at the terminology: Integrity comes from integrity, whole, 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, because the cars that have owner with ID 1, can be found in the table of owners as being of Pedro. The owner car with ID 2 can be found as belonging to Maria.

Now, imagine that we come to insert an EJB-6520, Celtic model and owner car with ID 3. It turns out that there is no owner of ID 3. If the database allows this inclusion, a referential integrity violation will occur, because a reference is being made to a non-existent entity. The same would happen if we want to change the owner of one of the cars by putting the owner’s ID as 3.

On the other hand, if we want to delete Maria from the database without deleting the DEF-4566 car and not change it, we will again have a referential integrity violation, because if the database allows this deletion to be made, the referential integrity will be violated by having a car that has as owner, a now nonexistent entity.

Most existing modern relational databases impose 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 recommended to use Innodb in these cases). It is also possible that the tables have been modeled without the foreign key being explicitly modeled in the database and only implied at the application level, and therefore the database will not be able to verify the integrity in this case.

If an integrity breach occurs, your database will display inconsistent records that point to non-existent entities, which tends to manifest in applications in the form of various types of problems.