All Forums General
ksaikrishna 26 posts Joined 08/11
29 Jun 2012
How to find the parent and child relationship between tables in Terdata by using Dictonary tables?

Hi,

In my project have many Facts and dimentions.I would like to know the relationship between those tables by using Query.

Does terdata store parent child relationship at dictonary tables? if yes please me know what are those tables/columns in that.

 

Thanks,

Saikrishna.k

 

 

Adeel Chaudhry 773 posts Joined 04/08
02 Jul 2012

I assume your question relates to referential integrity .... you can use following objects to get desired information:

 

 

DBC.All_RI_Children -> defined referential constraints from the child-parent perspective.

DBC.All_RI_Parents -> defined referential constraints from the parent-child perspective.

DBC.RI_Child_Tables -> tables in child-parent order. This view is similar to the All_RI_Children view, but returns the internal IDs of databases, tables, and columns.

DBC.RI_Distinct_Children -> tables in child-parent order without the duplication that could result from multi-column FKs.

DBC.RI_Distinct_Parents -> tables in parent-child order without the duplication that could result from multi-column FKs.

DBC.RI_Parent_Tables -> tables in parent-child order. This view is similar to the All_RI_Parents view, but returns the internal IDs of databases, tables, and columns.

 

HTH!

 

 

-- If you are stuck at something .... consider it an opportunity to think anew.

ksaikrishna 26 posts Joined 08/11
05 Jul 2012

CAn you frame the query with above tables?

 

Thanks,

Sai

WAQ 158 posts Joined 02/10
05 Jul 2012

From parent persective:

select * from DBC.All_RI_Children
where ParentTable = '<ur_parent_table>'

select * from DBC.All_RI_Parents
where ParentTable = '<ur_parent_table>'

select * from DBC.RI_Distinct_Children
where ParentTable = '<ur_parent_table>'

select * from DBC.RI_Distinct_Parents
where ParentTable = '<ur_parent_table>'

Depending what kind of information you need to extract from these tables/views.

ksaikrishna 26 posts Joined 08/11
06 Jul 2012

Hi  Waq,

I did not find single table from the above set of dictonary tables. even though there is a user difined tables in my database.

So,is there any other solution to get the data by using dbc.tables & dbc.columns?

 

Thanks,

sai

 

dnoeth 4628 posts Joined 11/04
06 Jul 2012

Hi Sai,

if you don't get an answer set there's probably no Foreign Key defined within your Create Table.

Then PK/FK relations are unknown to Teradata, you have to consult your logical model.

Dieter

Dieter

You must sign in to leave a comment.