Importance of Referential Integrity - Teradata Database

Database Introduction

Product
Teradata Database
Release Number
15.00
Language
English (United States)
Last Update
2018-09-25
dita:id
B035-1091
lifecycle
previous
Product Category
Teradata® Database

Referential integrity is important, because it keeps you from introducing errors into your database. Suppose you have an Order Parts table like the following.

 

Order Number

Part Number

Quantity

PK

Not Null

FK

FK

1

1

110

1

2

275

2

1

152

Part number and order number, each foreign keys in this relation, also form the composite primary key.

Suppose you were to delete the row defined by the primary key value 1 in the PART NUMBER table. The foreign key for the first and third rows in the ORDER PART table would now be inconsistent, because there would be no row in the PART NUMBER table with a primary key of 1 to support it. Such a situation shows a loss of referential integrity.

Teradata Database provides referential integrity to prevent this from happening. If you try to delete a row from the PART NUMBER table for which you have specified referential integrity, the database management system will not allow you to remove the row if the part number is referenced in child tables.

Besides data integrity and data consistency, referential integrity provides these benefits.

 

Benefit

Description

Increases development productivity

You do not need to code SQL statements to enforce referential integrity constraints because Teradata Database automatically enforces referential integrity.

Requires fewer written programs

All update activities are programmed to ensure that referential integrity constraints are not violated, because Teradata Database enforces referential integrity in all environments. Additional programs are not required.

Allows optimizations

Referential integrity allows optimizations to occur, such as join elimination.

For more information on the topics presented in this chapter, see the following Teradata Database books.

 

If you want to learn more about…

See…

Teradata Database Indexes

  • Database Design
  • SQL Fundamentals
  • Primary Indexes

  • Database Administration
  • Database Design
  • SQL Data Definition Language
  • SQL Fundamentals
  • No Primary Index (NoPI) Tables

  • Database Design
  • SQL Data Definition Language
  • SQL Data Manipulation Language
  • SQL Request and Transaction Processing
  • Row and Column Partitioning

  • Database Design
  • SQL Data Definition Language
  • Secondary Indexes

  • Database Administration
  • Database Design
  • SQL Data Definition Language
  • SQL Fundamentals
  • SQL Request and Transaction Processing
  • Join Indexes

    Hash Indexes

  • Database Design
  • SQL Data Definition Language
  • Index Specification

    SQL Data Manipulation Language

    Hashing

  • Database Design
  • SQL Data Definition Language
  • SQL Request and Transaction Processing
  • Identity column

    SQL Data Definition Language

    Normalization

    Database Design

    Referential integrity