I have 3 entity classes: Group, User, and Asset. Both Group and User have a 1-N relationship with Asset, both have a field of "Set<Asset> assets" that is @Persistent, and both have a primary key field named "id". When I run SchemaTool's datanucleus:schema-create from Maven I get the following error:
The column "ASSETS_ID_OWN" exists in table "`ASSET`" and has invalid metadata. The existing column is "<column/>
After a little trial and error, I found that SchemaTool was attempting to create a column of ASSET.ASSETS_ID_OWN for both Group and User, per http://www.datanucleus.org/products/datanucleus/jdo/orm/one_to_many_collection.html#fk_bi
I was able to work around the issue by renaming the field in one or both of the entity objects to something like "userId" or "groupId". Neither of these is appealing, because these are fields in the User and Group class respectively, so there's no need to embed scoping in the names.
My expectation here is that SchemaTool would resolve the collision somehow. The ambiguity of the naming chosen by the tool seems to be the root cause. The name gives no clue as to which table the key is for, which is a problem in general if you're manually inspecting the database. The easy fix would be to have SchemaTool prefix these foreign keys with the name of the source table.
I've attached a simple program that can illustrate this for you. I stripped my project down to the bare bones. I hope this suffices as a test case.
Just un-tar the attached file and do the following to reproduce the issue:
- Update javax.jdo.option.* to point at your own MySQL instance and authenticate with it.
- Create the "example" database in your MySQL instance.
- mvn clean package
- mvn datanucleus:schema-create
I'm probably going to end up moving to using a JOIN table for these relationships anyway, but this was definitely a barrier to getting up and going quickly with SchemaTool. It keeps it from doing the intuitive thing and "just working".