cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Need help navigating or using the PTC Community? Contact the community team. X

How can I list all foreign keys referencing a given table in oracle database?

smahishi
3-Newcomer

How can I list all foreign keys referencing a given table in oracle database?

I need to write script which will gather information on how tables are connected .

For example, Consider WTPART table i want get list of tables it is connected with and how it is connected.

Thank you in Advance.

3 REPLIES 3
ChrisSpartz
13-Aquamarine
(To:smahishi)

That will be difficult. The Windchill database doesn't use foreign key constraints, so there's no easy way to get a list of which table references which other tables.

Though, you may be able to use the classnamekey columns to get this information. For each foreign key column in a table, there is a corresponding classnamekey column that holds the name of the table that the foreign key references. For example, the ida3masterreference column of WTPart is a foreign key to the WTPartMaster table. There is a corresponding column in WTPart named classnamekeymasterreference which contains the text 'wt.part.WTPartMaster'. As another example, the ida3containerreference column of WTPart contains a foreign key to the container context of the part (which could be a product, project, library, etc). The corresponding classnamekeycontainerreferen column will contain text like 'wt.pdmlink.PDMLinkProduct', 'wt.projmgmt.admin.Project2', or 'wt.inf.library.WTLibrary', depending on the type of container.

What you could do is get a list of all the columns beginning with classnamekey for each table. Then, for each of these columns, get a list of the distinct values in that column. From that, you can figure out how the tables are connected.

TomU
23-Emerald IV
(To:ChrisSpartz)

Chris Spartz‌,

The Windchill database doesn't use foreign key constraints...

Why not?  Isn't that kind of the point of a relational database?  What benefit does not using foreign keys bring?

TomU
23-Emerald IV
(To:TomU)

Trying to answer my own question...

There are some interesting opinions about foreign keys on this page:  database - What's wrong with foreign keys? - Stack Overflow

I like this one:  "foreign keys are like brushing your teeth: go ahead, do without it, but careful when you smile"

Announcements


Top Tags