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. |