cascade-delete and not null foreign keys

[erikdhansen]

I have a simple app with a stateless session bean which acts as the interface to remote clients for interacting with a couple entity beans. Between the two entity beans there's a one-to-many unidirectional CMR with the many side set for cascade-delete so that they are all deleted when the one side is deleted.

The problem is I keep getting:
General error, message from server: "Cannot add or update a child row: a foreign key constraint fails"

When I go to delete the one side of the CMR.

I read that you can create a custom container with sync-on-commit-only to true to work around a not null foreign key constraint, but this doesn't appear to work.

A note on my database schema -- the table that is represented by the many side of the CMR has both a not null FK contraint for the PK of the one side of the relationship, as well as an ON DELETE RESTRICT contraint. I'd have expected the deletion of the one and many sides of the CMR would happen in a single transaction, but it appears that that's not the case.

In my MySQL log, I can see the DELETE for the one side of the CMR and then the UPDATE setting the FK field to NULL, and then a rollback. Shouldn't I see a DELETE for the many side? Why if the transaction is not yet committed would I see an SQL server error and a rollback?

[pdelgado0]

Hi erikdhansen,

I have a similar problem with the <cascade-delete> tag.
I thought it would have been related with a possible transaction bug or problem but, in my case, the problem was that on the database Jboss did not create correctly the CONSTRAINT. I mean, it creates something like this ...

Foreign Key constraints: xxxxxxx ON UPDATE NO ACTION ON DELETE NO ACTION

instead of

Foreign Key constraints: xxxxxxx ON UPDATE CASCADE ON DELETE CASCADE

I did not understand why Jboss put NO ACTION instead of CASCADE but the fact was this, so I solved running a little shell script that change the CONSTRAINT directly on the database after deploying and creating the tables.

The hole history is http://www.jboss.org/modules/bb/index.html?module=bb&op=viewtopic&t= I hope this may help you.

Pablo