Hi,
I've got a schema like...

people
(personid NUMBER, name VARCHAR);

friends
(personidA NUMBER, personidB NUMBER, yearsKnown NUMBER
foreign key (personidA) references people(personid),
foreign key (personidB) references people(personid))

so if a person has a friend that they've known for a number of years, it is necessary that the person is also a friend of the friend (get it?)

if A friend of B then B friend of A

I've mapped the relationship from one side like

<class name="person" table="people">
<id name="personid type="long" column="personid">
<generator class="sequence">
<param name="sequence">SQ_PERSONID</param>
</generator>
</id>

<map name="friends" table="friends" cascade="save-update">
<key column="personidA"/>
<map-key-many-to-many column="personidB" class="person"/>
<element column="yearsKnown" type="long"/>
</map>
</class>

The way it works now is if A is a friend of B I have 2 rows in the friends table

personidA,personidB,yearsKnown
A,B,2
B,A,2

if I delete person A, the row A,B,2 gets deleted, but not the row B,A,2, resulting in a foreign key exception. I'm looking for a way to declare in the mapping that when adding B as A's friend, that makes A a friend of B as well. Also if A is deleted, it is removed from B's friend list.

I'm getting around this now by manually looping through A's map and removing it from all it's friends lists before deleting it. It's not too bad but I'd like to know if there is a better way to map this kind of relationship. I've looked through the forums and found some simillar stuff but not quite like this.

If anyone has a suggestion as to how I would change my schema that would be acceptable as well. For example, if the solution required only 1 row per friend relationship that would be fine.

Thanks in advance,
Simon