1) Need to drop constraint for fastload and multiload (same as regular RI). 2) You need to enforce the RI with code or quality checks (same as No RI).
If you fail to do #2, then some of your queries will give the wrong results based on the underlying data.
For example, if you have a parent table and a child table and there are records in the child table for which there is no parent, the following query gies the wrong results:
select count(*) from child inner join parent ;
This will return the toal number of rows in child even if there are no rows in parent. This is becuase the optimizer assumes there is a row in parent for every row in child and since it has no need to access parent (no column selected), it doesn't actually do the join.
I was inquiring about soft RIs and found above notes. Can some one shed more light on soft RIs ? If data integrity can be guaranteed by ETL ,then soft RI is an impressive feature (even compared to complex join indices ) for performance improvement as it results in join elimination. Why then ,this feature is not used widely within Teradata practice?
Hi Adeel, what do you mean by "more effort and more CPU time for processing the same data-set"? Soft RI is a dummy, which is not checked by the DBMS.
Karam, you answered your own question: "If data integrity can be guaranteed by ETL "
If there is faulty data the result set will be wrong, if the optimizer eliminates a join.
And you need a to reference a USI/UPI column, which is sometimes not possible, because you didn't implement the USI or you have a temporal table with valid_from/valid_to and then it's not possible with a traditional FK. In TD13 this restriction "referencing a unique column" is removed, now you got a dummy FK and a dummy PK :-)
I have few additional point to share besides ETL check..These are from DBA perspective..
1. REFERENCES access right( which is used for soft RI implementation) is granted automatically to the CREATOR of object and implicitly to the OWNER of the object. This means that in a Teradata practice where DBAs use their individual id (rather than common id like SYSDBA etc) end up as the only person who can implement soft RI on tables created by his/her id . Further alter table wont be possible by another team member unless that table is dropped and recreated by another team member who in turn becomes the creator of the table then. In theory , this privilege will always be held by 1 DBA( the creator) rather than all. This is one such hassle in maintaining soft RI.
2. REFERENCES access right is granted on table level rather than database level. Hence more difficult to maintain with additional number of entries in access rights tables.
Hope it'll help to anyone considering +/- of soft RI