Hi,

I am using DerbyDB latest JDBC driver (10.4.2) with Hibernate 3.2.5 ga and I have one to many relationship for a project that contains a set of scans:

Project:

<set name="scanSet" cascade="save-update">
<key column="scan_id"/>
<one-to-many class="Scan"/>
</set>

DB contains Project table, Scan table and Scan table contains foreign key to a Project table (parent/children relationship).

Project.hbm.xml:

<class name="Project" table="project">
<id name="id" type="long" column="id">
<generator class="identity" />
</id>
<property name="name" type="string" column="name" not-null="true" length="100" />
<property name="description" type="string" column="description" length="255" />
<property name="basePath" type="string" column="base_path" length="700"/>
<property name="newEntryDate" type="timestamp" column="new_entry_date" />
<property name="modEntryDate" type="timestamp" column="mod_entry_date" />
<set name="scanSet" cascade="save-update">
<key column="scan_id"/>
<one-to-many class="Scan"/>
</set>
</class>

scan.hbm.xml:

<class name="Scan" table="scan">
<id name="id" type="long" column="id">
<generator class="identity" />
</id>
<one-to-one name="ruleSet" class="RuleSet" cascade="save-update"/>
<one-to-one name="scanConfigType" class="ScanConfigType" cascade="save-update"/>
<set name="scanTypes" cascade="all-delete-orphan">
<key column="scan_type_id"/>
<one-to-many class="ScanType"/>
</set>
<property name="name" type="string" column="name"/>
<property name="description" type="string" column="description" length="255" />
<property name="resourceEncoding" type="string" column="resource_encoding"/>
</class>

Inserts and selects works as expected, but when I try to delete a Scan, I am getting violation of foreign key constraint:

Caused by: java.sql.SQLException: DELETE on table 'SCAN' caused a violation of foreign key constraint 'FK1AECF55A71F4867' for key (1). The statement has been rolled back.
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
... 47 more
Caused by: ERROR 23503: DELETE on table 'SCAN' caused a violation of foreign key constraint 'FK1AECF55A71F4867' for key (1). The statement has been rolled back.
at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
at org.apache.derby.impl.sql.execute.ReferencedKeyRIChecker.doCheck(Unknown Source)
at org.apache.derby.impl.sql.execute.RISetChecker.doPKCheck(Unknown Source)
at org.apache.derby.impl.sql.execute.DeleteResultSet.collectAffectedRows(Unknown Source)
at org.apache.derby.impl.sql.execute.DeleteResultSet.open(Unknown Source)
at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)

It looks like a foreign key is the key mapping Scan(s) to a Project.

I tried to use Hibernate to delete either the Scan itself first and then remove it from the Project's Scan set or to to set scan set attribute cascade="all-delete-orphan" and then remove the scan from the Project's scan set, but both times the delete fails with violation of foreign key constraint.