Why Referential Integrity Is Important
Referential integrity is a mechanism to keep you from corrupting your database. Suppose you have a table like the following:
|
order_part |
|
|
|
order_num |
part_num |
quantity |
|
PK |
NN |
|
|
FK |
FK |
|
|
1 |
1 |
110 |
|
1 |
2 |
275 |
|
2 |
1 |
152 |
Part number and order number, each a foreign key in this relation, also form the composite primary key.
Suppose you were to go the part_number table and delete the row defined by the primary key value 1. The keys for the first and third rows in the order_part table are now parentless because there is no row in the part_number table with a primary key of 1 to support them. Such a situation exhibits a loss of referential integrity.
Now, suppose you had a mechanism to prevent this from happening? If you attempt to delete the row with a primary key value of 1 from the part_number table, the database management system does not allow you to do so. This is the way Teradata Database maintains referential integrity.
Besides data integrity and data consistency, referential integrity has the following benefits:
|
Benefit |
Description |
|
Increases development productivity |
It is not necessary to code SQL statements to enforce referential integrity constraints because Teradata automatically enforces Referential Integrity by means of declarative RI constraints. |
|
Requires fewer programs to be written |
All update activities are programmed to ensure that established declarative referential integrity constraints are not violated because Teradata enforces Referential Integrity in all environments: no additional programs are required. |