Finding Foreign Key for Primary Key Column

Database
Enthusiast

Finding Foreign Key for Primary Key Column

Hi,

Is there is a way to find foreign key columns that references to a primary key ?

i.e I have table PrimaryTable which has Col1 as primary key. I want to find out the tables having columns that have foreign key constraint defined on it which reffers to PromaryTable.col1.

Regards
2 REPLIES
Enthusiast

Re: Finding Foreign Key for Primary Key Column

You can use the following system views.
DBC.RI_CHILD_TABLES
DBC.RI_DISTINCT_CHILDREN
DBC.RI_DISTINCT_PARENTS
DBC.RI_PARENT_TABLES
Teradata Employee

Re: Finding Foreign Key for Primary Key Column

If you are using V2R6 then this will identify all dependencies. Replace the literals with the db and table you want to trace all dependencies from. I had to use a staging table because the view dbc.RI_Distinct_Children was causing missing data in the WITH RECURSIVE clause.

Create Volatile Table ri As (
Select
childdb,
childtable,
parentdb,
parenttable
From dbc.RI_Distinct_Children
) With data
On
Commit preserve rows;

With Recursive RI_LOOKUP (parentdb, parenttable, depth) As
(
Select root.parentdb, root.parenttable, 0 As depth
From ri root
Where root.childtable='CHILD_TABLE'
And root.childdb='CHILD_DB'
Union All
Select indirect.parentdb, indirect.parenttable, direct.depth + 1
From RI_LOOKUP direct, ri indirect
Where direct.parenttable = indirect.childtable
)
Select *
From RI_LOOKUP
Order By Depth Desc;

Drop Table ri;