ER Diagrammer connected to Oracle DB

Toad Data Point & Toad Intelligence Central
1

I'm a new user and am trying to use the ER Diagrammer with an Oracle Database. I expected that selecting a table into the diagrammer would bring in all other tables with relationships. That's not what I get - no other tables are brought in and no relationships shown. I chose a real common one HZ_PARTIES. I dragged another table that I know has a relationship thinking it might link together, but it doesn't. I've attached a screen shot.

Are my expectations incorrect or am I missing something in the set up of Toad for Data Analysis?

CrossTab2.png
CrossTab2.png667×668 96.5 KB

2

The ER Diagrammer brings in tables that have foreign keys. The table you mention only has a primary index.

3

Thanks for the response.

Perhaps I need a DB 101 refresher. I thought that the Fields on the HZ_PARTIES table that referenced primary keys on other tables were foreign keys. For HZ_PARTIES there are many. I’ve attached a screenshot from Oracle eTRM that shows what it believes are the foreign keys foreign keys.

For example, on the HZ_PARTIES Table the key is PARTY_ID, and the foreign key to the HZ_PARTY_SITES table is HZ_PARTY_SITE_ID. Am I mistaken?

Now, I don’t kow how those are identified within the application, but it looks like Oracle believes there are foreign keys.
TDA_ER_Screenshot_HZ_Keys.doc (181 KB)

4

Your understanding is correct. If you drag HZ_PARTIES to the ER diagram from the Object Palette, you should see tables that are refernced by HZ_PARTIES (“parent” tables) and tables that reference HZ_PARTIES (“child” tables). As an example, you can find the list of “parent” and “child” tables with these two queries:
– “parent” tables for HZ_PARTIES
– i.e. tables that are referenced by HZ_PARTIES
select b.owner, b.table_name
from all_constraints a, all_constraints b
where
a.constraint_type = ‘R’
and a.owner = ‘AR’ and a.table_name = ‘HZ_PARTIES’
and b.owner = a.r_owner and b.constraint_name = a.r_constraint_name
and b.constraint_type in (‘P’, ‘U’) ;
– “child” tables for HZ_PARTIES
– i.e. tables that reference HZ_PARTIES
select b.owner, b.table_name
from all_constraints a, all_constraints b
where
a.constraint_type in (‘P’, ‘U’)
and a.owner = ‘AR’ and a.table_name = ‘HZ_PARTIES’
and b.r_owner = a.owner and b.r_constraint_name = a.constraint_name
and b.constraint_type =‘R’ ;

Of course those parent/child tables can themselves have parent/child tables.

After looking at your screenshots, I would ask you to do the following:
go to main menu item Tools->Options, Database folder, Diagramming page
In the “Show” portion of the Database-Diagramming options, is the “Relations” checkbox checked? And what value do you have in the “Maximum references depth:” drop-down?

5

Below is a screenshot of the settings. I ran the queries but got no
records returned.

I'm a Business Analyst and not a DBA so I'm not really familiar with the
tables you are querying and not sure what to expect as an output. I'll
try to work through it - in the mean time, I hope the screenshot below
helps.

image001.png
image001.png1280×800 86.3 KB

6

from all_constraints a*–, all_constraints b*

where

a.constraint_type in (‘P’, ‘U’)

and a.owner = ‘AR’ and a.table_name = ‘HZ_PARTIES’

–and b.r_owner = a.owner

–and b.r_constraint_name = a.constraint_name

*–and b.constraint_type =‘R’
*Only one record is returned and there is no value in the r_owner field. That’s why the original query fails. Now, not understanding what this table is showing or what it does, I can’t determine why that’s an issue, only that its the reason why the original query doesn’t return any records. I hope this helps.

I analyzed the ‘child’ query and see why no records are returned. I modified the query as shown belownow

select a.owner,a.table_name, a.r_owner, a.constraint_type

7

Hello. In the third post in this thread, you have an attachment named TDA_ER_Screenshot_HZ_Keys.doc that shows relationships between the table AR.HZ_PARTIES and other tales. Where did you get that information in that screenshot? Was it from the Oracle documentation?

There is a technical difference between constraints and logical relationships.

Let’s take an example:
I have two tables, ORDERS and ORDERS_DETAIL. Both have a column named ORDER_NUMBER. There is only one entry with the same ORDER_NUMBER in ORDERS, but multiple entries can have the same ORDER_NUMBER in ORDERS_DETAIL. The ORDER_NUMBER in ORDERS_DETAIL should always be present in table ORDERS. I will use the expressions “parent” table and “child” table - ORDERS is the “parent” table and ORDERS_DETAIL is the “child” table.

I could write a program for order entry that always makes sure that the ORDER_NUMBER in the ORDERS_DETAIL (“child”) table is already present in the ORDERS (“parent”) table. In that case, I, as the programmer, know that there is a relationship between the two tables, and that ORDER_NUMBER cannot be in the detail table without being in the main orders table - because I wrote my program to check for that.

But the database will not know this. The only way that the database will know about the relationship is if I create these things:

  1. A PRIMARY KEY constraint on ORDERS with PRIMARY KEY column ORDER_NUMBER;
  2. a FOREIGN KEY constraint on ORDERS_DETAIL saying that column ORDER_NUMBER references a column named ORDER_NUMBER in table ORDERS
    (the terminology here is that table ORDERS_DETAIL has a FOREIGN KEY constraint that references table ORDERS - the FOREIGN KEY constraint has one column, ORDER_NUMBER)

If those two constraints are not there, then as far as the database is concerned, the tables are not releated.

I asked you to run queries to look for a PRIMARY KEY constraint and corresponding FOREIGN KEY constraints. If both are not present, then there is no relationship between the tables from the database point of view. Toad for Data Analysis (TDA) “asks” the database if there is a relationship between the two tables, and if the database doesn’t know of a relationship between the two tables, then TDA will not know of a relationship either.

One of the queries was looking for a PRIMARY KEY constraint on table AR.HZ_PARTIES and corresponding FOREIGN KEY CONSTRAINTS referencing AR.HZ_PARTIES (looking for relationships with AR.HZ_PARTIES as the “parent” table.) There weren’t any. You modified the query to only look for a PRIMARY KEY constraint on table AR.HZ_PARTIES, and there was a PRIMARY KEY constraint - but there was no corresponding FOREIGN KEY constraint on another table that referenced this primary key constraint.
The other query is looking for FOREIGN KEY constraints on table AR.HZ_PARTIES that reference other tables (looking for relationships with AR.HZ_PARTIES as the “child” table). None were found either.
Because both queries returned 0 rows, TDA thinks that your table AR.HZ_PARTIES is not related to any other table.

I hope this is clear. I know it can be confusing. If you want, I can provide you with some SQL statements that will show what I am trying to explain.

8

This is perfectly clear and thank you for the level of detail.

The screenshot showing the foreign keys came from
http://metalink.oracle.com/Knowledge/eTRM. Once you navigate to the
eTRM page you can select DBA and FND data based on either an object or a
schema for a particular application version. I looked at the FND data
for the HZ_PARTIES object and got the list I sent (there were over a
hundred entries - I just took a screenshot of the first page).

I guess that Oracle defines these relationships in some other manner
because they do clearly exist. For example, a Party Site (residing in
HZ_PARTY_SITES) cannot be created without a corresponding parent Party
(residing in HZ_PARTIES). I’m no architecture expert so I can’t
determine where that constraint is enforced, but it sure doesn’t seem to
be at the database level.

Thanks again for answering the question, this was very educational.

Kathy