no primary key on generated join table

[Henno Vermeulen <he...@huizemolenaar.nl>]

When I use @ManyToMany on a List or Set and let OpenJPA generate my database schema, it creates a join table with two foreign key columns that allows duplicates.

Is there a way to let OpenJPA generate a compound primary key on both the foreign key columns or should I manually add these in the database?
Or is not a problem when a join table has no primary key?

RE: no primary key on generated join table

[Henno Vermeulen <he...@huizemolenaar.nl>]

I worked around my issue by having all of my ManyToMany fields annotated like:

	@ManyToMany
	@JoinTable(joinColumns = { @JoinColumn(nullable = false) }, inverseJoinColumns = { @JoinColumn(nullable = false) })
	private List<MailingList> mailingLists = new ArrayList<MailingList>();

This ensures that the columns are generated as non nullable. The boilerplate code in @JoinTable can simply be copy/pasted to each ManyToMany field.
For each join table I add the primary key by filling the following SQL with the table and column names that are generated by OpenJPA:

ALTER TABLE [dbo].[$table$]  ADD  CONSTRAINT PK_$table$ PRIMARY KEY CLUSTERED
(
 $col1$ ASC,
 $col2$ ASC
)


-----Oorspronkelijk bericht-----
Van: Henno Vermeulen [mailto:henno@huizemolenaar.nl] 
Verzonden: donderdag 20 september 2012 16:48
Aan: 'users@openjpa.apache.org'
Onderwerp: RE: no primary key on generated join table

To only partially answer my own question, I can have OpenJPA generate a unique constraint when I add something like:

	@ManyToMany
	@JoinTable(joinColumns = { @JoinColumn(name = "CONTACT_ID") }, 
	inverseJoinColumns = { @JoinColumn(name = "MAILINGLISTS_ID") }, 
	uniqueConstraints = { @UniqueConstraint(columnNames = {
			"CONTACT_ID", "MAILINGLISTS_ID" }) })
	private List<MailingList> mailingLists = new ArrayList<MailingList>();

Here I used the default column names that OpenJPA generates for me.
However this is not a primary key. Plus I think it's rather ugly having to add boiler plate like this; isn't JPA intended to minimize having to write boilerplate code?


-----Oorspronkelijk bericht-----
Van: Henno Vermeulen [mailto:henno@huizemolenaar.nl] 
Verzonden: donderdag 20 september 2012 16:37
Aan: 'users@openjpa.apache.org'
Onderwerp: no primary key on generated join table

When I use @ManyToMany on a List or Set and let OpenJPA generate my database schema, it creates a join table with two foreign key columns that allows duplicates.

Is there a way to let OpenJPA generate a compound primary key on both the foreign key columns or should I manually add these in the database?
Or is not a problem when a join table has no primary key?

RE: no primary key on generated join table

[Henno Vermeulen <he...@huizemolenaar.nl>]

To only partially answer my own question, I can have OpenJPA generate a unique constraint when I add something like:

	@ManyToMany
	@JoinTable(joinColumns = { @JoinColumn(name = "CONTACT_ID") }, 
	inverseJoinColumns = { @JoinColumn(name = "MAILINGLISTS_ID") }, 
	uniqueConstraints = { @UniqueConstraint(columnNames = {
			"CONTACT_ID", "MAILINGLISTS_ID" }) })
	private List<MailingList> mailingLists = new ArrayList<MailingList>();

Here I used the default column names that OpenJPA generates for me.
However this is not a primary key. Plus I think it's rather ugly having to add boiler plate like this; isn't JPA intended to minimize having to write boilerplate code?


-----Oorspronkelijk bericht-----
Van: Henno Vermeulen [mailto:henno@huizemolenaar.nl] 
Verzonden: donderdag 20 september 2012 16:37
Aan: 'users@openjpa.apache.org'
Onderwerp: no primary key on generated join table

When I use @ManyToMany on a List or Set and let OpenJPA generate my database schema, it creates a join table with two foreign key columns that allows duplicates.

Is there a way to let OpenJPA generate a compound primary key on both the foreign key columns or should I manually add these in the database?
Or is not a problem when a join table has no primary key?