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. View the original post : http://www.jboss.org/index.html?module=bb&op=viewtopic&p=3845213#3845213 Reply to the post : http://www.jboss.org/index.html?module=bb&op=posting&mode=reply&p=3845213 ------------------------------------------------------- SF.Net email is sponsored by Shop4tech.com-Lowest price on Blank Media 100pk Sonic DVD-R 4x for only $29 -100pk Sonic DVD+R for only $33 Save 50% off Retail on Ink & Toner - Free Shipping and Free Gift. http://www.shop4tech.com/z/Inkjet_Cartridges/9_108_r285 _______________________________________________ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user
