Using a JoinTable, getting unique constraint violtions

[smokingapipe]

Let's say I'm making an accounting system. I have Invoices, and on Invoices I have Products. Every invoice can have a set of one or more products, and there could be duplicates. Every product could show up on any number of invoices. The relationship is uni-directional. The Product doesn't need to have a collection of Invoices that contain it.

How do I map this?

It seems like what I need is a JoinTable specification, but when I create it, I get a table with a unique constraint on invoice_id. That should not be the case; I should be able to use that invoice_id multiple times to be able to specify a set of products.

What I need is a join table that would look like this:

invoice_id product_id
33 58
33 37
33 962

but it's failing on the uniqueness constraint of invoice_id. Any ideas on how to specify this?

[smokingapipe]

I figured out the problem, but not the solution.

Here's the problem:

I have a uni-directional Collection in my Invoice:

@Entity class Invoice {

 @OneToMany Collection<Product> getProducts();

}

This results in creating a join table, with a unique constraint on the product_id column, which doesn't work because the Collection can have the same Product multiple times.

The naive solution would be a table that looks like:

invoice_id product_id
33 88
33 88

But that's not a proper relation. Rows (tuples) must be unique.

What is needed is a third column, which is a row ID. I can't see anything in the docs about how I could do that. Any suggestions?

[smokingapipe]

I ended up creating a placeholder entity. If that's the only way to do this, that's an unfortunate defect in the EJB3 spec. If there is some way please let me know.

[fhh]

This is not a one-to-many relationship but a (unidirectional) many-to-many. (One product belongs to many invoices, many invoices belong to one one product).

Regards

Felix