Hi, When I was playing with this sample, I didn’t encounter errors described above. In the pure version of sample code, there is no insert in dim tables and thus referential integrity (RI) can’t be violated. When you add simple “insert into values“ for them, it still works. I’d suspect transaction and such stuff, see also Teradata documentation “SQL Reference: Data Definition Statements, p. 576”; let’s space here for some supreme being to teach us more.
I'd ask the more important question: "Why do you intent to design the DW with RI?” I’ve never seen it on Teradata. The relations are usually kept in the information delivery layer, like Microstrategy, Cognos Framework or Business Objects Universe.
Well, I'm more pragmatic then theoretic. Regarding question # 2 - WITH CHECK OPTION controls RI on transactional level not a row level. Begin and end of transaction is influenced by session, tool and connection setup (Teradata or ANSI session mode; implicit transaction). Error ocures, when transaction is finished.
btw: RI decrease performance and increase data volume. Still curious why use it?
thx. - Petr, referential integrity is needed if I want to create aggregate join indexes on my data mart. without referential integrity I would have to create a lot of join indexes, with referential integrity I can build only one and it will cover a lot of queries.
Thank you for explanation, it's interesting, never heard about that, but used AJI just for couple of time. Does it help Optimizer? The common limitations of AJI are outer joins and aggtegated functions, which can be used (SUM, COUNT), but it is not probably this case. Usualy AJIs are done on higher level of dimension like support a monthly numbers from daily datamart. I'd be glad, if you can disclose more info, I'm fun of db design and tuning.
Reasons to implement RI – Data integrity and consistency – Increases development productivity – users don’t have to code SQL statements to enforce referential constraints – Optimized performance – Teradata chooses the most efficient method to enforce the referential constraints – User applications may rely on Referential Integrity for their functionality
Petr lets suppose that we have 3 dimensions and 1 facts table (like in the example above). lets suppose that join index (or aji) is based on 3 dims and facts (all tables inner joined).
1. with or without referential integrity: if you submit query which joins dim1, dim2, dim3 and facts index can be used 2. with referential integrity: if you submit query which joins dim1 and facts index can be used because optimizer knows that facts rows reference rows from other dims (so he knows that inner join will not throw away those records) 3. without referential integrity if you submit query which joins dim1 and facts index cannot be used because optimizer does not know if rows from facts reference rows from other dims and optimizer does not know if it is one-to-many or many-to-one or anything else.