Archives of the TeradataForum
Message Posted: Wed, 04 Aug 1999 @ 17:27:27 GMT
Thanks for your comments.
Regarding when you say that if you enforce RI, you will never get the parent/child violations that you want to know about....... I interpret RI differently. When RI is enforced, you do know the parent/child violations immediately. Inserts/Updates won't occur with RI enforced when violations occur. Whether these inserts or updates occur from OLTP or load utilities, you will know about them. Unfortunately, the Teradata buffer load utilities will not work with RI constraints.
Secondly, I do agree that when migrating from another database, RI violations can occur. In our case, it was not because of existing RI on the source Oracle db, but additional RI implemented when building the Teradata database to make it more complete.
Lastly, I also agree that violations identified are a catalyst to change the OLTP system to prevent future violations within the warehouse. In our environment, when we first built our warehouse, we did just that.
Unfortunately, with as many programs we run within OLTP and Batch, it takes time, sometimes a very long time, to implement the required changes. Also, we have found over the past 5 years, applications on the host system (BULL Mainframe running GCOS8 / IIDS-II hierarchical databases) don't always consider checks for parent/child relationships as built in the warehouse. This causes the host data extracts loaded into the warehouse to have integrity issues so we use Oracle RDBMS RI constraints on our current Oracle database to catch/prevent invalid inserts/updates to the warehouse.
As we move into the Teradata arena, because we see host applications sometimes miss these checks, my concern is that the Teradata SQL scripts you mentioned to do these checks (my definition....another application) may also sometimes miss new checks as the warehouse develops and expands causing data integrity issues.
To sum it up, I believe the more you can do at the database level to prevent integrity issues, the more responsible the data will be. RDBMS level RI constraints put one less burden on application development to remember, or forget, as time goes on.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|