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:
- A PRIMARY KEY constraint on ORDERS with PRIMARY KEY column ORDER_NUMBER;
- 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.