Consider the employee and payroll tables for any business.
With referential integrity constraints, the two tables work together as one. When one table gets updated, the other table also gets updated.
The following case depicts a useful referential integrity scenario.
Looking for a better career, Mr. Clark Johnson leaves his company. Clark Johnson is deleted from the employee table.
The payroll table, however, does not get updated because the payroll clerk simply forgets to do so. Consequently, Mr. Clark Johnson keeps getting paid.
With good database design, referential integrity relationship would have been defined on these tables. They would have been linked and, depending on the defined constraints, the deletion of Clark Johnson from the employee table could not be performed unless it was accompanied by the deletion of Clark Johnson from the payroll table.
Besides data integrity and data consistency, referential integrity also has the benefits listed in the following table.
| Benefit | Description |
|---|---|
| Increases development productivity | It is not necessary to code SQL statements to enforce referential constraints. Teradata Database automatically enforces referential integrity. |
| Requires fewer programs to be written | All update activities are programmed to ensure that referential constraints are not violated. Teradata Database enforces referential integrity in all environments. No additional programs are required. |
| Improves performance | Teradata Database chooses the most efficient method to enforce the referential constraints. Teradata Database can optimize queries based on the fact that there is referential integrity. |