I am trying to define an m,n relationship:

Reference * <-> * Component

<list name="references" table="reference2component" lazy="false">
<key column="component_id"/>
<index column="order_in" type="integer"/>
<many-to-many class="bacrule1.Reference" column="reference_id"/>
</list>

to delete the link , and only the link, between a reference and a component I do:

component.getReferences().remove(reference);

Hibernate executes:
delete from reference2component where component_id=?

though I don't understand the SQL (shouldn't it be "where component_id=? and reference_id=?") everything behaves fine: only the row (reference_id, component_id) in the n,m table is deleted.

However, when I use the same mapping for another m,n relationship

Xref * <-> * Proteome

with exactly the same type of definitions in the Hibernate mapping files and the database schema, I get:

Hibernate: delete from xref2proteome where proteome_id=? and order_in=?
Hibernate: update xref2proteome set uri=? where proteome_id=? and order_in=?
Feb 4, 2004 6:38:35 PM net.sf.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 0, SQLState: null
Feb 4, 2004 6:38:35 PM net.sf.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: ERROR: Cannot insert a duplicate key into unique index pk_xref2proteome

the *only* difference between the 2 cases is that the <id> for Reference is of type int, and the <id> for Xref is of type String.

Why is there an update statement in one case and not the other?
why is order_in included in the WHERE clause of the DELETE statement in one case and not the other?

I am completely puzzled by this behaviour and any help is greatly appreciated.

thanks

Isabelle

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>

<class name="bacrule1.Reference" table="reference">
<id column="reference_id" name="id" type="long" unsaved-value="0">
<generator class="sequence">
<param name="sequence">row_ids</param>
</generator>
</id>
<property column="authors" name="authors" type="string"/>
<property column="medline" length="8" name="medline" type="long"/>
<property column="pubmed" length="8" name="pubmed" type="long"/>
<property column="title" length="200" name="title" type="string"/>
<property column="citation" length="200" name="citation" not-null="true" type="string"/>
</class>

<class name="bacrule1.GeneLocation" table="component">
<id column="component_id" name="id" type="long" unsaved-value="0">
<generator class="sequence">
<param name="sequence">row_ids</param>
</generator>
</id>
<property column="order_in" name="index" type="integer" not-null="true"/>
<property column="component_name" length="60" name="name" type="string"/>
<property column="regexp" length="80" name="regexp" type="string"/>
<list name="references" table="reference2component" lazy="false">
<key column="component_id"/>
<index column="order_in" type="integer"/>
<many-to-many class="bacrule1.Reference" column="reference_id"/>
</list>
</class>

<class name="bacrule1.DbXref" table="db_xref">
<id column="uri" length="200" name="uri" type="string" unsaved-value="null">
<generator class="assigned"/>
</id>
<property column="dbname" length="20" name="name" type="string"/>
<property column="type_code" length="1" name="type" not-null="true" type="integer"/>
</class>

<class name="bacrule1.Proteome" table="proteome">
<id column="proteome_id" name="id" type="long" unsaved-value="0">
<generator class="sequence">
<param name="sequence">row_ids</param>
</generator>
</id>
<property column="strain" length="100" name="strain" type="string"/>
<list name="dbxrefs" table="xref2proteome" lazy="false">
<key column="proteome_id"/>
<index column="order_in" type="integer"/>
<many-to-many column="uri" class="bacrule1.DbXref"/>
</list>
</class>

</hibernate-mapping>

Table "public.xref2proteome"
Column | Type | Modifiers
-------------+------------------------+-----------
uri | character varying(200) | not null
proteome_id | integer | not null
order_in | integer | not null
Indexes: pk_xref2proteome primary key btree (uri, proteome_id),
uq_xref2_order unique btree (proteome_id, order_in)
Foreign Key constraints: fk_xref2_xref FOREIGN KEY (uri) REFERENCES db_xref(uri) ON UPDATE CASCADE ON DELETE CASCADE,
fk_xref2_proteome FOREIGN KEY (proteome_id) REFERENCES proteome(proteome_id) ON UPDATE CASCADE ON DELETE CASCADE

Table "public.reference2component"
Column | Type | Modifiers
--------------+---------+-----------
reference_id | integer | not null
component_id | integer | not null
order_in | integer | not null
Indexes: pk_reference2component primary key btree (reference_id, component_id),
uq_reference2_order unique btree (component_id, order_in)
Foreign Key constraints: fk_reference2_component FOREIGN KEY (component_id) REFERENCES component(component_id) ON UPDATE CASCADE ON DELETE CASCADE,
fk_reference2_reference FOREIGN KEY (reference_id) REFERENCES reference(reference_id) ON UPDATE CASCADE ON DELETE CASCADE

I've found the solution to my question: the problem was indeed the type of the <id> of one end of the n,m relationship. The behaviour of the many-to-many mapping of indexed lists is not the same whether the <id> is a native type, eg. long, or an Object, eg. String. We get a constraint violation upon delete because an Object is nullable.

the solution:

1. change the unsaved-value for the child object with the <id> of type String from "null" to "none"

<class name="bacrule1.DbXref" table="db_xref">
<id column="uri" length="200" name="uri" type="string" unsaved-value="none">

2. remove the item from the list, then put all the remaining items in a new List and persist the new List
List xrefs = proteome.getDbxrefs();
// removing item will create a null value in list
// and cause constraint violation on commit
proteome.getDbxrefs().remove(xref);

// Create new list for storing all non null values
List cleanXrefs = new ArrayList();
for (Iterator iter = xrefs.iterator(); iter.hasNext();) {
DbXref element = (DbXref) iter.next();
if (element != null) {
cleanXrefs.add(element);
}
}

// save new list
proteome.setDbxrefs(cleanXrefs);

tx.commit();