17.10 - Why Referential Integrity Is Important - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL Fundamentals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1141-171K
Language
English (United States)

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.

Vantage automatically enforces referential integrity.

Requires fewer programs to be written All update activities are programmed to ensure that referential constraints are not violated.

Vantage enforces referential integrity in all environments.

No additional programs are required.

Improves performance Vantage chooses the most efficient method to enforce the referential constraints.

Vantage can optimize queries based on the fact that there is referential integrity.