Hello experts,
I'm using v4.1.3 of Oracle SQL Developer Data Modeler and it is my first time using this particular data modelling tool.
I originally reversed engineered in the data model from an Oracle 11g database, with a view to make some significant enhancements later. The process of reverse engineering included the tables and their primary key constraints which has used multi-part natural keys to uniquely identify the record, as well as materialized views. The database schema reversed did not contain any foreign keys, so I was using the tool to add these in before continuing with any enhancements. For the most part this has worked fine (for approximately 60 tables) as nearly all of the tables have accurate primary keys, so I just edited the properties of a table, clicked on the Foreign Key link, clicked on the Add Foreign Key icon, selected the referenced table (which also selected the constraint) from the LOV and assigned the local columns to map to the referenced columns.
However, for two of my tables, the list of values (LOV) that I used to choose the local columns that map to the referenced columns in the parent table is not accurate - it does not list all the columns from my local table, or at least not the ones I wish to nominate as the local foreign key columns. I've checked (he says!) that the primary keys are accurate in both the tables and they seem to be. I do not know why a tiny list of columns are being displayed (it shows 3 columns in the LOV for each of the 3 referenced columns correctly identified from the referenced primary key constraint - for two referenced columns it shows the same 3 values and for other it shows a different set of 3 columns). This foreign key constraint needs to map 3 local columns to 3 referenced columns (I mentioned the model I'm starting with uses natural keys).
Has anyone come across this behaviour before? The use of multi-part natural keys, cascaded down to their children and grand-children is pretty common in this schema and I have had no problems linking any of the other tables together with foreign keys. Is there any property I can set to influence how it tries to match the columns names to use?
cheers,
John