@ManyToMany, Set, join table and primary key

[Klesk <cg...@hotmail.com>]

Hi all,

I have a problem with the @ManyToMany annotation on a Set container (OpenJPA
2.1.0) :
When I generate my database (Derby 10.7) the generated join table have two
foreign keys but no primary key !

My question is : How can I add a primary key (or unique) constraint on the
join table with OpenJPA to prevent duplicate row inside DB table ?

Best regards,
Guillaume

--
View this message in context: http://openjpa.208410.n2.nabble.com/ManyToMany-Set-join-table-and-primary-key-tp6173427p6173427.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: @ManyToMany, Set, join table and primary key

[Heath Thomann <jp...@gmail.com>]

This is a peculiar question.  I may need a little more details and/or some
code snippets.  However, with my limited understanding of the question at
this time, I will have a couple options to offer.
You state: "When I generate my database (Derby 10.7) the generated join
table have two foreign keys but no primary key ! ".  Couple things here:
first, I assume you are letting OpenJPA generate the tables?  Second, if
OpenJPA generates the tables, it will generate a join table with the two
foreign keys as you have found.  The JPA 2.0 spec, section 2.10.4 states the
naming rules around the created table (note that this is for a bidirectional
M-M):

There is a join table that is named A_B (owner name first). This join table
has two foreign key
columns. One foreign key column refers to table A and has the same type as
the primary key of
table A. The name of this foreign key column is formed as the concatenation
of the following:
the name of the relationship property or field of entity B; "_"; the name of
the primary key column
in table A. The other foreign key column refers to table B and has the same
type as the primary
key of table B. The name of this foreign key column is formed as the
concatenation of the
following: the name of the relationship property or field of entity A; "_";
the name of the primary
key column in table B.


The purpose of a ManyToMany join table is so that you can have full
multiplicity from type A to type B entities.  The single purpose of the
table is to maintain that relationship.  The primary keys are on the
entities themselves.  I think we'd need to know why you need a primary key
on the join table.  One could be added via executing an alter table DDL
directly on the table.  Or, if you just need a constraint on one of the
columns (which would severely limit the usefulness of a many to many), it
could be added directly to the JoinTable definition like so:

@Entity
public class Department {
...
    @ManyToMany(cascade=CascadeType.ALL)
    @JoinTable(name="DEPT_EMP",
               joinColumns=@JoinColumn(name="DEPT_ID"),
               inverseJoinColumns=@JoinColumn(name="EMP_ID"),
               uniqueConstraints=@UniqueConstraint(columnNames={"DEPT_ID"}))
    private List<Employee> employees;
...
}

This will add a unique constraint on the DEPT_ID column in the join table
if/when OpenJPA is used to generate the join table  (via SynchronizeMappings
or the MappingTool).

Thanks,

Heath


On Tue, Mar 15, 2011 at 10:34 AM, Klesk <cg...@hotmail.com> wrote:

> Hi all,
>
> I have a problem with the @ManyToMany annotation on a Set container
> (OpenJPA
> 2.1.0) :
> When I generate my database (Derby 10.7) the generated join table have two
> foreign keys but no primary key !
>
> My question is : How can I add a primary key (or unique) constraint on the
> join table with OpenJPA to prevent duplicate row inside DB table ?
>
> Best regards,
> Guillaume
>
> --
> View this message in context:
> http://openjpa.208410.n2.nabble.com/ManyToMany-Set-join-table-and-primary-key-tp6173427p6173427.html
> Sent from the OpenJPA Users mailing list archive at Nabble.com.
>