What is Referential Integrity?
Referential integrity defines relationships between tables based on a primary key and a foreign key. It describes the columns in the referencing table, which are a
foreign key in the referenced table.
Referential integrity is a robust mechanism to ensure data consistency in a database.
What is Soft Referential Integrity?
Soft Referential Integrity is a hint to the Optimizer that a relationship between tables exists. The Optimizer will rely on this information; it will not enforce referential integrity, but it assumes that it is user imposed.
How is Soft Referential Integrity used?
Soft Referential Integrity is used for INNER JOIN Elimination in views.
If we select only columns from the referencing table (primary key side) of a view containing an inner join with soft referential integrity defined between two tables, the inner join can be avoided. Here is an example:
REPLACE VIEW TradeInfo AS
t01.TraderId = t02.TraderId
We assume a primary key (Trader) / foreign key (Trades) soft referential integrity defined on column TraderId.
If we execute below SQL statement, the Optimizer will not join to table “Trades”, as all referenced columns are from table “Trader”, and the defined soft referential integrity ensures that each row in “Trades” will be available in “Trader”. Therefore, there is no need to “filter” non-matching rows with the INNER JOIN.
We could use “hard” referential integrity to achieve the join elimination, but enforcing “hard” referential integrity causes performance overhead we may want to avoid. Teradata soft referential integrity helps to improve performance.
One important fact about “soft” referential integrity, which has to be pointed out: The Optimizer trusts that we ensure the integrity of the relationship!
The syntax to define soft referential integrity is as follows. The column’s data type has to be the same for both tables, and the referenced column has to be indexed uniquely:CREATE MULTISET TABLE Trader
REFERENCES WITH NO CHECK OPTION TradeAccount(TraderId),
) UNIQUE PRIMARY INDEX (TraderId)