fbpx

Teradata Soft Referential Integrity

By Roland Wenzlofsky

March 14, 2017


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
SELECT
t01.TraderId,t01.TraderName,t01.TraderCountry,
t02.AccountCurrency,t02.BaseCurrency
FROM
Trader t01
INNER JOIN
TradeAccount t02
ON
t01.TraderId = t02.TraderId
;[/su_panel]

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
(
TraderId INTEGER
REFERENCES WITH NO CHECK OPTION TradeAccount(TraderId),
TraderName VARCHAR(255),

TraderCountry CHAR(03)
) UNIQUE PRIMARY INDEX (TraderId)
;

__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"var(--tcb-skin-color-0)"}},"gradients":[]},"original":{"colors":{"62516":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Previous Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Next Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"rgb(255, 0, 0)"}},"gradients":[]}}]}__CONFIG_colors_palette__
GET OUR TERADATA BOOK

Roland Wenzlofsky

Roland Wenzlofsky is an experienced freelance Teradata Consultant & Performance Trainer. Born in Austria's capital Vienna, he is building and tuning some of the largest Teradata Data Warehouses in the European financial and telecommunication sectors for more than 20 years. He has played all the roles of developer, designer, business analyst, and project manager. Therefore, he knows like no other the problems and obstacles that make many data warehouse projects fail and all the tricks and tips that will help you succeed.

  • 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. The 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: http://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

    >