problem with cascade delete and relationships

[raj_kumar]

Does permission have foreign keys to other tables?
You should not use cascade-delete in database.

[aloubyansky]

Does permission have foreign keys to other tables?
You should not use cascade-delete in database.

[raj_kumar]

hi...
Thanks alex...for ur reply.

Alex i had tried all kinds of combinations even i had removed
cascade-delete from the database also but the same error i'm getting
every time ....i'm not able to spot that....and Permission dont have any child
table except SwitchPermission. ....SwitchPermission dosent have any child.
can u tell me where the things are going wrong .... according to Trace
i can find that when i removes Permission then it starts cascade-delete on
SwithPermission even the ejbRemove method also gets executed ......but
then i dont know why it throws Remove Exception...i'm not able to
sort it out...pls help...

Thanks
Raj...
Logic Eastern

[raj_kumar]

hi alex ....
now i got that root of the problem... i hope this is the bug of jboss...
look, here are 3 tables which are OperationDetail, Permission and SwitchPermission ... where OperationDetail is parent of Permission which have not-null foreign key in Permission say(OperationDetailID)... and Permission is an Parent of SwitchPermission....There is a cascade-delete set between Permission and SwitchPermission..... and when i tries to delete
Permission then i got from the trace that it tries put "null" on the OperationDetailID which is a not-null FK in Permission table... and then the
error occurs .....this is exactly what happens.. even after that i had set a <not-null/>
in the jbosscmp-jdbc.xml...then also its not working ... can u tell me whether
its an bug or what exactly i have to do to make it work...


You can check the last Trace which i had sent to u .....here is small trace which i am sending to u .....from which u can have an idea.

 invoke called for ctx org.jboss.ejb.EntityEnterpriseContext@1c17f5a, tx=TransactionImpl:XidImpl [FormatId=257, GlobalId=le52//65, BranchQual=]
2004-12-10 13:46:45,180 TRACE [org.jboss.ejb.plugins.EntitySynchronizationInterceptor] register, ctx=org.jboss.ejb.EntityEnterpriseContext@1c17f5a, tx=TransactionImpl:XidImpl [FormatId=257, GlobalId=le52//65, BranchQual=]
2004-12-10 13:46:45,180 TRACE [org.jboss.ejb.plugins.cmp.jdbc.JDBCRelationInterceptor.PermissionEJB] Remove relation: field=switchPermissions id=127 relatedId=1
2004-12-10 13:46:45,180 TRACE [org.jboss.ejb.plugins.EntitySynchronizationInterceptor] register, ctx=org.jboss.ejb.EntityEnterpriseContext@1c17f5a, tx=TransactionImpl:XidImpl [FormatId=257, GlobalId=le52//65, BranchQual=]
2004-12-10 13:46:45,180 TRACE [org.jboss.ejb.plugins.EntityInstanceInterceptor] End invoke, key=127, ctx=org.jboss.ejb.EntityEnterpriseContext@1c17f5a
2004-12-10 13:46:45,180 TRACE [org.jboss.ejb.plugins.EntityLockInterceptor] End invoke, key=127
2004-12-10 13:46:45,180 TRACE [org.jboss.ejb.plugins.LogInterceptor] End method=<no method>
2004-12-10 13:46:45,180 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreEntityCommand.PermissionEJB] Executing SQL: UPDATE permission SET UserID=?, OperationDetailID=? WHERE PermissionID=?
2004-12-10 13:46:45,180 TRACE [org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCCMP2xFieldBridge.PermissionEJB#userID] Set parameter: index=1, jdbcType=VARCHAR, value=NULL
2004-12-10 13:46:45,180 TRACE [org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCCMP2xFieldBridge.PermissionEJB#operationDetailID] Set parameter: index=2, jdbcType=INTEGER, value=NULL
2004-12-10 13:46:45,180 TRACE [org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCCMP2xFieldBridge.PermissionEJB#permissionID] Set parameter: index=3, jdbcType=INTEGER, value=127
2004-12-10 13:46:45,195 TRACE [org.jboss.ejb.plugins.EntityInstanceInterceptor] End invoke, key=1, ctx=org.jboss.ejb.EntityEnterpriseContext@db7e08
2004-12-10 13:46:45,195 TRACE [org.jboss.ejb.plugins.EntityLockInterceptor] End invoke, key=1
2004-12-10 13:46:45,195 TRACE [org.jboss.ejb.plugins.TxInterceptorCMT] TxInterceptorCMT: In finally
2004-12-10 13:46:45,195 ERROR [org.jboss.ejb.plugins.LogInterceptor] TransactionRolledbackLocalException in method: public abstract void javax.ejb.EJBLocalObject.remove() throws javax.ejb.RemoveException,javax.ejb.EJBException, causedBy:
java.sql.SQLException: General error, message from server: "Cannot add or update a child row: a foreign key constraint fails"

if i removes NOT-NULL from the database and make it DEFAULT-NULL then
everything works fine ...but this is not what i want...

Thanks
Raj......
Logic Eastern

[aloubyansky]

Yes, it's a bug in JBossCMP. Please, submit a bug report at jira.jboss.com, JBoss Application Server project, CMP.

[raj_kumar]

hi alex,
thanks for ur reply.... i had made a post in jira.jboss.org........

Thanks
Raj...........