I need some information regarding soft RI.Can someone provide the information?
I read that specifying WITH NO CHECK OPTION in table definition specifies Soft RI and any error message that would be returned when there is a RI violation would not be returned to user when Soft RI is specified. I perceive this as though inserting rows into a non-RI table. But, my questions are (1) what is the advantage of using this kind of RI? (2) how does soft RI help in Join elimination? (3) Does teradata recognize Soft RI while processing the joins? How the joins are done? (4) How safe are we, when considering data corruption due to violation of RI..i mean to say that in cases where the user specifies soft RI and he does not take care of violations and accidentally inserts a violation row?
Please do not revert back to documentation link. I just need simple information regarding above questions
The optimizer will assume RI constraints have been enforced. Pro: You may get join elimination or other query plan improvements. Con: If data does not actually conform to the declared constraints, you may get the wrong answer.
The DBMS will do nothing to enforce or validate RI. Pro: You incur no system overhead for updates; "soft" RI is valid even with utilities that don't support normal RI. Con: The application must maintain RI, or risk getting the wrong answer.