Howto model multiple FKs

[Kevin Ternes]

I am modeling a new Oracle RDBMS schema using EA 6.1.788.  Some of my tables will have multiple FKs into another using the same target column.

When I model this, I follow the example in Help for creating composite foreign keys.

When EA generates the DDL for me, it gives:

Code: [Select]


ALTER TABLE REQUEST ADD CONSTRAINT REQUEST_WEIGHT_FK
FOREIGN KEY (NEW_WEIGHT_ID, CUR_WEIGHT_ID) REFERENCES WEIGHT (WEIGHT_ID)


To which Oracle replies:
ORA-02256: number of referencing columns must match referenced columns

It would be better if EA generated something like the following:

Code: [Select]


ALTER TABLE REQUEST ADD CONSTRAINT REQUEST_NEW_WEIGHT_FK
FOREIGN KEY (NEW_WEIGHT_ID) REFERENCES WEIGHT (WEIGHT_ID)

ALTER TABLE REQUEST ADD CONSTRAINT REQUEST_CUR_WEIGHT_FK
FOREIGN KEY (CUR_WEIGHT_ID) REFERENCES WEIGHT (WEIGHT_ID)


Can anyone tell me how to properly model this?

[«Midnight»]

While I don't have the correct syntax handy, I can shed some light on your problem.

You seem to be trying to say that there is a single relationship between the two tables, where two columns taken together in the child table reference a single column in the parent. Oracle is arguing because of the table structures, since it can figure out that the combination of the two child columns does not match the structure of the parent primary key.

In the data modelling profile used by EA (et al) keys and other constraints are modelled as operations within classes with the <<table>> tablestereotype. You have essentially added a single operation to your child table, with both columns as the key value. What you want to do is add two separate[/] operations. Stereotype each as FK. Each of them will have a single column (as you describe in your 'desired' result. Both of these will point to the parent primary key.

You can do this by adding an association between the two tables. Make sure the association is seleced, right click on it, and choose Foreign Keys... Fill out the information for the first relation, then go through the same process again for the second.

HTH, David

[Kevin Ternes]

David,
That is it!  Simplicity itself.
Thank you.