What is Referential Integrity?
Referential integrity establishes table relationships through primary and foreign keys, specifying the referencing table columns corresponding to the referenced table’s foreign key.
Referential integrity is a reliable mechanism for maintaining data consistency in a database.
What is Soft Referential Integrity?
Soft Referential Integrity informs the Optimizer of a table-relationship, which the Optimizer will utilize without enforcing referential integrity, assuming instead that the user will ensure it.
How is Soft Referential Integrity used?
Soft referential integrity facilitates INNER JOIN elimination in views.
By exclusively selecting columns from the referencing table’s primary key side in a view where an inner join exists with soft referential integrity between two tables, the Optimizer can bypass the need for the inner join. Consider the following illustration:
REPLACE VIEW TradeInfo AS
t01.TraderId = t02.TraderId
The TraderId column defines soft referential integrity between the primary and foreign keys (Trader) (Trades).
Executing the following SQL statement will not result in the Optimizer joining to the “Trades” table. This is because all referenced columns are from the “Trader” table, and the defined soft referential integrity guarantees that all rows in “Trades” will be present in “Trader”. As a result, there is no necessity to filter non-matching rows using an INNER JOIN.
Teradata’s soft referential integrity option can improve performance when achieving join elimination. While we could opt for “hard” referential integrity, doing so would result in performance overhead that we may wish to avoid.
It is crucial to note that “soft” referential integrity relies on our ability to guarantee the relationship’s integrity. The Optimizer places its trust in us.
To establish soft referential integrity, ensure that the column’s data type is identical in both tables and that the referenced column is uniquely indexed.
CREATE MULTISET TABLE Trader
REFERENCES WITH NO CHECK OPTION TradeAccount(TraderId),
) UNIQUE PRIMARY INDEX (TraderId)