Description:
The foreign keys that result from a many-to-many relationship operation seem to be represented strangely in the table property inspector.
How to repeat:
To recreate:
- Create table_one
- Create table_two
- Create a many-to-many between these
Table image in the ERD will show two items under INDEXES:
- Primary
- FK (for one of the two parent tables)
In the table properties inspector for the relation table, the FOREIGN KEY tab confirms that two FK's are created as expected
The INDEXES tab shows two indexes:
- PRIMARY (which is a composite of the two FK's)
- FK (just relating to one of the two tables)
I would have expected the composite PRIMARY key, but the presence of the added FK index confuses me and it seems arbitrary.
An image in one of NetBeans' tutorials that uses workbench follows this exact procedure, except that in their ERD for the relation table, Workbench shows:
- PRIMARY
- FK1
- FK2
See last ERD image in this tutorial: http://netbeans.org/kb/docs/javaee/ecommerce/data-model.html
Perhaps that was a prior version of Workbench and the behavior has changed?
Suggested fix:
Since two foreign keys are correctly created (one from each parent table) and since foreign keys are indexes, it would seem that they should indeed both show up in the list of INDEXES for the relation table.
But it also makes sense that the primary index for the new table be a composite of the two FK's.
So, seeing only (PRIMARY) -OR- (PRIMARY + FK1 + FK2) in the INDEX list would have seemed logical, but (PRIMARY + FK1) seems confusing.