Teradata: Referential Integrity

Teradata: Referential Integrity


Teradata support two ways to define Referential Integrity
Hard Referential Integrity(RI): Hard RI is general way of defining contraint using Foreign key in a table referencing other table.


Soft Referential Integrity(RI): Soft RI is Teradata way of defining contraint where Teradata will assume that user will maintain integrity themselves.





Hard vs Soft Referential Integrity


Hard Referential Integrity

Soft Referential Integrity

Referenced Column

Must be defined as Unique in parent table.

Not necessary to be defined as Unique in parent table.

System Overhead

System always have to check referenced column value in parent table.

System will not check referenced column value in parent table. No system overhead

Onus for Maintaining Integrity

System will maintain by itself and throw error if there is any violation.

Teradata will assume that users will ensure integrity of data between parent & child table.