CMR fields and referential integrity

[timfox]

For the sake of argument, I have two entity beans:

Order and OrderItem:

There is a one-many relationship:

"An order is composed of many order items".

The order-item table has a foreign key (orderid) mapping back to the order table.

When I create a new order order item, I set the CMP fields in the ejbCreate() method, and I set the order CMR field in the ejbPostCreate() method, as per the ejb spec I believe.

Jboss then first attempts to insert a new order item with a NULL orderid, and then do an UPDATE to set the orderid.

Of course the INSERT fails, since the foreign key column is NOT NULL as per standard sensible database design.

I am stumped here - does Jboss expect me to drop referential integrity on my database so I can persist ejbs with CMR relations??

[timfox]

forgot to say I am using 3.0RC3

[pluellen]

>>does Jboss expect me to drop referential integrity on my database so I can persist ejbs with CMR relations??

Tim,

I'm not the expert here but, yes, I think at this point your foreign key columns must allow null. See thread http://www.jboss.org/modules/bb/index.html?module=bb&op=viewtopic&t=forums/

[timfox]

hi pluellen-

thanks for your reply.

i've always been taught that having nullable foreign keys is a big no no.

and anyway I can't drop the foreign keys since the database is already in production and used by other apps..... ouch

another possible solution is to have a before insert trigger on the table and insert a "special" foreign key that points at a special dummy record when null is attempted to be inserted.

this is a kludge though and I'd rather not have to do it.

hopefully this will be implemented soon as it seems a BIG oversight...

[dsundstrom]

It is not an oversite. I knew exaxtally what I was forcing users into when I wrote it.

Support for this is not easy. The EJB spec does alot to make it even harder, and you are only looking at creation; there is an equal problem on removal. This will be added in a future release (3.1 most likely).

[timfox]

Hi Dain-

sorry for assuming it's an oversight - a bit presumptious on my behalf.

anyway, the trigger workaround should allow me to do what i want now.

cheers