All Forums Database
eejimkos 73 posts Joined 01/12
24 Jul 2014
Identify Multicolumn Foreign key on TD 14

Hello,
 
How can i identify all the single and multicolumn which are foreign keys , meaning when implementing either Soft Ri or not.
 
Thank you.

Raja_KT 1246 posts Joined 07/09
24 Jul 2014

Is this what you are looking for?
http://forums.teradata.com/forum/database/how-display-list-of-foreign-key-constraints-including-with-no-check-option-setting
 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

eejimkos 73 posts Joined 01/12
25 Jul 2014

Hello,
Thank you for your time and reply.
I have seen this post , but i wanted to avoid this solution . There are system tables which keep the information of foreign key (parent - child ) but the granularity is on single column, even if the foreign key is a multicolumn .
I wanted to see if with sql  ,i could achieve that.
 
Thanks.
 

dnoeth 4628 posts Joined 11/04
25 Jul 2014

It's an easy task to provide the info about multicolumn FKs using the dbc views as each column of a  multicolumns FK is stored as a row in those views (similar to dbc.IndicesV).

Dieter

eejimkos 73 posts Joined 01/12
25 Jul 2014

 Hi,
 
Finally , i will conclude again , never work tired. Thank you all for your time and answers.
 

sel 
 indexID 
,childdb as DB_FK_
,childtable as tbl_fk_
,max(case when rn1 =    1        then           trim(childkeycolumn) else '' end)||
max(case when rn1=     	2	 then      ','||trim(childkeycolumn) else '' end)||
max(case when rn1 = 	3	 then      ','||trim(childkeycolumn) else '' end)||
max(case when rn1 = 	4	 then      ','||trim(childkeycolumn) else '' end)|| 
max(case when rn1 = 	5	 then      ','||trim(childkeycolumn) else '' end)||
 max(case when rn1 = 	6	 then      ','||trim(childkeycolumn) else '' end)  as FK_
,ParentDB as db_pk_
,ParentTable as tbl_pk_
,max(case when rn1 =     1       then           trim(ParentKeyColumn) else '' end)||
max(case when rn1= 	 2	 then      ','||trim(ParentKeyColumn) else '' end)||
max(case when rn1=  	 3	 then      ','||trim(ParentKeyColumn) else '' end)||
max(case when rn1 = 	 4	 then      ','||trim(ParentKeyColumn) else '' end)||
max(case when rn1 = 	 5	 then      ','||trim(ParentKeyColumn) else '' end)||
max(case when rn1 = 	 6	 then      ','||trim(ParentKeyColumn) else '' end)  as PK_
from
(
sel 
 row_number() over (partition by childdb,childtable,childdb,childtable,IndexID order by childdb,childtable,childdb,childtable,IndexID ) as rn1
,childdb
,childtable 
,childkeycolumn  
,ParentDB
,ParentTable
,ParentKeyColumn
,IndexID
from  DBC.All_RI_ChildrenV  
 )x
 group by 1,2,3,5,6

 
 
 

You must sign in to leave a comment.