In order to remove redundant data in database tables,
the design of the database is normalized
- almost always resulting in multiple tables linked
together using foreign keys. This introduces a new problem
because the database engine will need to keep track
of the relationship to ensure that each foreign key
must actually link to a valid value in another table.
This is call referential integrity.
When referential integrity is enforced, all foreign
keys are properly matched with existing data in the
referenced table. This simply means that if Table A
contains a foreign key that references to a record in
Table B, the record in Table B must exist.
What happens if the user tries to create a new record
in the product table with a category_id of "0003"?
There is no such record in the category table. As such,
such a record in the product table will contain an invalid
reference to the category table.
What happens if the user tries to delete the 1st record
in the category table? Deleting the 1st record will
effectively invalidate the category reference in the
first 2 records in the product table.
If referential integrity is applied, both the above
transactions would not be allowed by the database engine.
The database engine enforces referential integrity through
the use of referential constraints and triggers. Referential
constraints prevent situations as describe in scenario
1 above from happening while triggers prevent situations
as described in scenario 2 above from happening.
