Foreign key violation - why is child deleted first in 1-1 re
I am having trouble setting up a relationship in my deployment descriptors for a simple relationship in my database.
I have two tables, Group and Address. A group has zero or one physical addresses. There is a foreign key on
Group.PhysicalAddressKey that forces the existance of a related address. An address isn't directly related
back to a group because address is used for various entities - Group, Member, Company, etc. Therefore, I'm trying
to set up a uni-directional relationship between a Group and its physical address. When a Group is deleted from the
database, I would like its related physical address to be deleted, too.
More specifically, here's what I would like to happen:
1. Update Group, set physicalAddressKey = NULL
2. Delete Address record
3. Delete Group record
What seems to be happening (from the log) is:
1. Delete Address record
2. Foreign key violation
Specifically:
2004-08-16 08:03:46,406 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCFindByPrimaryKeyQuery.Group#findByPrimaryKey] Executing SQL: SELECT t0_Group.groupKey FROM CdhpGroup t0_Group WHERE t0_Group.groupKey=? 2004-08-16 08:03:46,406 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.Group] Executing SQL: SELECT groupName, physicalAddressKey, mailingAddressKey, feeBankAccountNumber, feeBankKey, createEnrollmentVerificationLetterInd, createDateTime, createUserId, lastUpdateDateTime, lastUpdateUserId FROM CdhpGroup WHERE (groupKey=?) 2004-08-16 08:03:46,421 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadRelationCommand.Group] load relation SQL: SELECT groupKey, effDate FROM CDHPGroupId WHERE (groupKey=?) 2004-08-16 08:03:46,437 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadRelationCommand.Group] load relation SQL: SELECT benefitPlanKey FROM CDHPBenefitPlan WHERE (groupKey=?) 2004-08-16 08:03:46,453 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadRelationCommand.Group] load relation SQL: SELECT physicalAddressKey FROM CdhpGroup WHERE (groupKey=?) 2004-08-16 08:03:46,500 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCRemoveEntityCommand.Address] Executing SQL: DELETE FROM CDHPAddress WHERE addressKey=? 2004-08-16 08:03:46,515 ERROR [org.jboss.ejb.plugins.cmp.jdbc.JDBCRemoveEntityCommand.Address] Could not remove 3 java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]DELETE statement conflicted with COLUMN REFERENCE constraint 'FK__CdhpGroup__Physi__6B6FCE9C'. The conflict occurred in database 'ASIFSA', table 'CdhpGroup', column 'PhysicalAddressKey'. at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source) at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source) ... javax.ejb.RemoveException,javax.ejb.EJBException, causedBy: javax.ejb.RemoveException: Could not remove 3
I tried removing the foreign key constraint from the database to see what would happen. Everything is happy (except for
my DBA who insists on the foreign key). The container:
1. Deletes Address record
2. Deletes Group record
2004-08-16 08:05:28,093 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCFindByPrimaryKeyQuery.Group#findByPrimaryKey] Executing SQL: SELECT t0_Group.groupKey FROM CdhpGroup t0_Group WHERE t0_Group.groupKey=? 2004-08-16 08:05:28,109 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.Group] Executing SQL: SELECT groupName, physicalAddressKey, mailingAddressKey, feeBankAccountNumber, feeBankKey, createEnrollmentVerificationLetterInd, createDateTime, createUserId, lastUpdateDateTime, lastUpdateUserId FROM CdhpGroup WHERE (groupKey=?) 2004-08-16 08:05:28,109 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadRelationCommand.Group] load relation SQL: SELECT groupKey, effDate FROM CDHPGroupId WHERE (groupKey=?) 2004-08-16 08:05:28,125 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadRelationCommand.Group] load relation SQL: SELECT benefitPlanKey FROM CDHPBenefitPlan WHERE (groupKey=?) 2004-08-16 08:05:28,125 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadRelationCommand.Group] load relation SQL: SELECT physicalAddressKey FROM CdhpGroup WHERE (groupKey=?) 2004-08-16 08:05:28,140 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCRemoveEntityCommand.Address] Executing SQL: DELETE FROM CDHPAddress WHERE addressKey=? 2004-08-16 08:05:28,156 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCRemoveEntityCommand.Group] Executing SQL: DELETE FROM CdhpGroup WHERE groupKey=?
Here is the relationship from ejb-jar.xml:
<ejb-relation > <ejb-relation-name>group-physicaladdress</ejb-relation-name> <ejb-relationship-role > <ejb-relationship-role-name>physicaladdress-has-group</ejb-relationship-role-name> <multiplicity>One</multiplicity> <cascade-delete/> <relationship-role-source > <ejb-name>Address</ejb-name> </relationship-role-source> </ejb-relationship-role> <ejb-relationship-role > <ejb-relationship-role-name>group-has-physicaladdress</ejb-relationship-role-name> <multiplicity>One</multiplicity> <relationship-role-source > <ejb-name>Group</ejb-name> </relationship-role-source> <cmr-field > <cmr-field-name>physicalAddress</cmr-field-name> </cmr-field> </ejb-relationship-role> </ejb-relation>
And the relationship in jbosscmp-jdbc.xml:
<ejb-relation> <ejb-relation-name>group-physicaladdress</ejb-relation-name> <ejb-relationship-role> <ejb-relationship-role-name> physicaladdress-has-group </ejb-relationship-role-name> <key-fields> <key-field> <field-name>addressKey</field-name> <column-name>physicalAddressKey</column-name> </key-field> </key-fields> </ejb-relationship-role> <ejb-relationship-role> <ejb-relationship-role-name> group-has-physicaladdress </ejb-relationship-role-name> <key-fields/> </ejb-relationship-role> </ejb-relation>
I'm sure I must be missing a simple configuration option.
It's almost funny. A while back, I had to use the <batch-cascade-delete> element in jbosscmp-jdbc.xml to force this
delete-child-first behavior (it was a different situation, 1-many relationship with non-nullable keys). But
now, the container looks as if it's using the <batch-cascade-delete> functionality when I don't want it.
For what it's worth, I am using jboss version 3.2.5, and SQL Server 2000.
Thanks for any suggestions.
Sue.
