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
SELECT
t01.TraderId,t01.TraderName,t01.TraderCountry,
t02.AccountCurrency,t02.BaseCurrency
FROM
Trader t01
INNER JOIN
TradeAccount t02
ON
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
(
TraderId INTEGER
REFERENCES WITH NO CHECK OPTION TradeAccount(TraderId),
TraderName VARCHAR(255),
...
TraderCountry CHAR(03)
) UNIQUE PRIMARY INDEX (TraderId)
;
  • How can we identify if table has soft or Hard RI using DBC tables?

  • Avatar
    Johannes Vink says:

    The explain plan will only show what it is going to do, the explain will not show what is considered but left behind.

    Few observations:
    1) Soft RI requires that the fields are EXACTLY the same. Physical design is important. I ran across a STORE_ID CHAR(4) and STORE_ID VARCHAR(4). That was not allowed…

    2) Soft RI is a must for Join Indexes (JI). Maybe only certain types, but see here for an example: https://developer.teradata.com/blog/mtmoura/2011/01/lets-talk-about-aggregate-join-indexes-aji

    Either I skipped large parts of Teradata manuals, or this subject is not so covered. It took me some time to figure out why an (A)JI was not used in a query.

    2 points from the site above:
    – The parent key columns must be either a unique primary index (UPI) or a unique secondary index (USI).
    – The foreign and parent keys must have the same number of columns and their data types must match.

  • I am not sure if there is a special “hint” in the execution plan that the soft RI is used. Maybe somebody else can answer this?

    Of course, you can see that soft RI is used, as the join steps are missing in the plan. But of course, this can be difficult to find out for huge plans.

  • Avatar
    Washim Nawaz says:

    Thanks for the Article, My question is is there any keyword in the Explain Plan to see if the soft RI is being used for join elimination? Trying to find if it’s possible to know soft-RI being used by Optimizer for JOIN elimination for a query with complex join involving many tables with soft RIs on it. I appreciate the help.

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    You might also like

    >