jpa - How can I create multiple composite foreign key constraints which reference the same table? -

i using eclipselink 2.4.0 , trying find way generate following ddl statements:

alter table delta add constraint deltafk1 foreign key (appkey, newrevision) references revision (appkey, revision); alter table delta add constraint deltafk2 foreign key (appkey, oldrevision) references revision (appkey, revision); 

each row in delta table represents changes between 2 specified revisions , primary key made of appkey, newrevision, , oldrevision. first alter statement being generated following relationship annotations defined in delta.java class:

public class delta {  @embeddedid private deltapk deltapk;  @manytoone @primarykeyjoincolumns({        @primarykeyjoincolumn(name="appkey", referencedcolumnname="appkey"),        @primarykeyjoincolumn(name="newrevision", referencedcolumnname="revision") }) private revision newrevision;  @manytoone @primarykeyjoincolumns({        @primarykeyjoincolumn(name="appkey", referencedcolumnname="appkey"),        @primarykeyjoincolumn(name="oldrevision", referencedcolumnname="revision") }) private revision oldrevision; 

the name values of each primarykeyjoincolumn, ‘appkey’, ‘oldrevision’, , ‘newrevision’ fields defined in deltapk class , referencedcolumnname values fields defined in revision class.

i’ve tried bunch of variations, , closest i’ve gotten when comment out primarykeyjoincolumn ‘appkey’ oldrevision object. second alter statement generated, includes oldrevision value (not appkey), expect. thoughts or suggestions on how accomplish appreciated.

i had walk through eclipselink source code in debugger while before found out how solve one. turns out there 1 key part of problem did not mention in original question (because didn't know part of problem). fields 'appkey' , 'revision' revision class not primary key table, however, make uniqueness constraint:

@table(     name = "revision",     uniqueconstraints = @uniqueconstraint(columnnames = {"appkey", "revision"}) ) 

it turns out eclipselink generating name of foreign key constraint partially based on ordering of columnnames in uniqueness constraint. caused both of foreign key constraints generated same name led second 1 being ignored , not generated. (see following methods in org.eclipse.persistence.tools.schemaframework.tabledefinition if you'd put details.)

buildforeignkeyconstraint(list<string> fkfieldnames, list<string> pkfieldnames, tabledefinition targettable, databaseplatform platform) buildforeignkeyconstraintname(string tablename, string fieldname, int maximumnamelength, databaseplatform platform) addforeignkeyconstraint(foreignkeyconstraint foreignkey) 

in short, when reordered values cloumnnames so:

@uniqueconstraint(columnnames = {"revision", "appkey"}) 

i got 2 differently named foreign key constraints (because of 2 mappings between 'revision' , 'newrevison' , 'oldrevision') shown below:

alter table delta add constraint delta_newrevision foreign key (newrevision, appkey) references revision (revision, appkey) alter table delta add constraint delta_oldrevision foreign key (oldrevision, appkey) references revision (revision, appkey) 

you may different output different database platforms (i using derby), think general issue , solution same. hope explained clear enough else out in future.