16.20 - Why Referential Integrity Is Important - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantageâ„¢ SQL Fundamentals

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-02
dita:mapPath
zce1519094756513.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval

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.