We have a bidirectional many-to-many mapping with a list on one side and a set on the inverse side. I found that if I remove the first item from the list, the primary key uniqueness constraint is violated.

Unfortunately, I don't have a debug log with me here (I'm at home right now). I'll give pseudocode for the SQL that was generated, because this will better illustrate what the problem is anyhow:

Imagine that the join table has three columns: Table1Key_ID, Table1Key_Index and Table2Key_ID. My list has three items, the first of which is being deleted. Here's a sample table:



1) delete from JoinTable where Table1Key_ID = 44 and Table2Key_ID = 77
2) update JoinTable set Table2Key_ID=66 where Table1Key_ID=44 and Table1Key_Index=0
3) update JoinTable set Table2Key_ID=77 where Table1Key_ID=44 and Table1Key_Index=1

That second query fails, because the primary key on the join table is Table1Key_ID, Table2Key_ID, and the 44-66 pair already exists at index 1.

We *could* make the primary key use all three columns and this problem would go away. But, that's not really the relationship we want to model, because a pair should not appear in here more than once.

We could also replace the collection with a new collection, which would cause Hibernate to delete all of the rows and re-add them. That's not ideal, either, obviously...

Is there some other way to avoid violating this constraint?

Thanks in advance...
Kevin

Here is the mapping:
<list
name="appearsOnPage"
table="Article_AppearsOnPage_Page"
lazy="true"
inverse="false"
cascade="none"
>

<key
column="Article_AppearsOnPage_ID"
/>

<index
column="AppearsOnPage_Index"
/>

<many-to-many
class="org.jstor.model.repository.Page"
column="Page_ID"
outer-join="auto"
/>

</list>

and from the inverse side:
<set
name="articlesOnPage"
table="Article_AppearsOnPage_Page"
lazy="true"
inverse="true"
cascade="none"
sort="unsorted"
>

<key
column="Page_ID"
/>

<many-to-many
class="org.jstor.model.repository.Article"
column="Article_AppearsOnPage_ID"
outer-join="auto"
/>

</set>



Hibernate 2.1.2, MySQL 4 w/INNO DB.[/code]

This is strange, you use a list in one side and a set on the other side, the semantic is broken.

I double checked with the Advanced Mapping chapter of Hibernate In Action earlier today... When doing a bidirectional many-to-many relationship, you can only maintain ordering from one direction. By putting inverse=true on the Set side, that side of the relationship is ignored for updates. Only the List side is used when doing updates, that way ordering is maintained properly.

p.228 in Chapter 6 of Hibernate In Action talks about using different collections on each side of the bidirectional relationship and the requirement that the indexed side not have inverse=true.

Kevin

The book is correct but from a Java perspective, you have to know that a Set contains unique objects and a list does not. That's why I said it is strange, and I don't like this sort of mapping.
And your DBA seems to agree with me. You unique constraint shows that.

Ahh, I misunderstood what it was you didn't like about the semantics. What I really want is a Set on one side (the inverse side) and an "ordered set" on the other side. The ordering is important, which is why I used a List, but something should not appear in that list more than once.

I don't remember seeing a Hibernate collection with those semantics, unfortunately.

Kevin

You can use a set with order-by or sort attributes I'd say ...

Thanks for the suggestion. I hadn't considered that possibility.

Unfortunately, though, one other requirement we have is that we need to be able to insert elements in random positions in the collection (as you can with a List). Sadly, a collection using the Set interface won't quite do it.

Here is why the model looks like this:

* an article can appear on one or more pages (but it wouldn't make sense for it to know about a given page twice). This is ordered in order to be able to traverse the article's pages, even if they are out of sequence in the original published work.

* a page can contain zero or more articles. The order of the articles is not important, but an article should only be listed once for the page.

I don't think what we're trying to do is crazy, but perhaps this is an appropriate instance to create a separate class to handle the relationship. It just hadn't seemed necessary to do this previously, because the relationship itself has no other attributes.

Kevin

I'm dealing with the same problem. What I'm going to try now is create an extra class, with the ordering and the connection to the other elements in that class. The rest will be connected using <one-to-many> relations. I would consider this a work around and I'm not happy, but if it works. I'll keep you posted on my progress.

this is my idea:


Update: I've tried it: this works. but It isn't clean nor efficient.

Regards,
- Vincent

continuing discussion here: http://forum.hibernate.org/viewtopic.php?t=932130[/url]