Re: Different between soft referential integrity and hard referential integrity
"Hard" referential integrity is the "normal" referential integrity that enforces any RI constraints and ensures that any data loaded into the tables meets the RI rules. You should keep in mind that neither Multiload or Fastload allow the target table to have foreign key references. Tpump does allow this.
"Soft" referential integrity is a feature that is more about accessing the data than about loading it. Soft referential integrity does not enforce any RI constraints. However, when you specify soft RI, you are telling the optimizer that the foreign key references do exist. Therefore, it is your job to make sure that is true.
For example, if I have a sales table that references a store number, I could set up a soft referential integrity constraint between the "sales" table and the "store" table. That would tell the optimizer that all store numbers that exist in the sales table also exist in the store table (even if they don't actually exist). The optimizer can then use this information to avoid joining to the store table. For example, if I coded an inner join betwen the sales table and the store table, and I did not reference any other columns from the store table except for the store number, the optimizer could avoid actually joining to the store table at all.
This is useful since we sometimes set up views to join all of the "dimension" tables to the "fact" table in a view to make things easier. By using soft RI, the optimizer can join only to the dimension tables that it needs to and can avoid joins to the rest.
When you set up soft referential integrity, you have to ensure that the foreign key does in fact exist if you want to get the "correct" answer. Otherwise, the optimizer may avoid joining to the foriegn key table and will produce an incorrect answer.