Violation of foreign key constraint on delete

[lubosp <po...@gmail.com>]

Hi,

I am using DerbyDB latest JDBC driver with Hibernate 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).

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.

-- 
View this message in context: http://www.nabble.com/Violation-of-foreign-key-constraint-on-delete-tp20795597p20795597.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.