Database foreign keys must be "not null"?

[Marco DeFreitas]

After much hair pulling (a CMP workbook that didn't correspond to the DTD, etc) and research into the forum archives, I finally got a simple container managed relation example to work.

My example was the classic 1:N relation of an order:lineitem. I had the foreign key in the lineitem table (which referenced the order table) set up as NOT NULL. When I tried to add a lineitem to an order, I got an error that a null was being inserted into a non-null field. After a bit of thinking I removed the NOT NULL restriction on the foreign key and it worked.

Is this the way it has to be? It would seem that making the foreign key NOT NULL is useful for database integrity.

Also, I assume two database hits are being used (one to insert the lineitem with a null foreign key and one to update the foreign key). I guess this is the way it has to be (given the CMR sematics)?

This is my first attempt at using Entity Beans and I've got to admit the overhead scares me. Is there a good book someone can recommend on the "right way" to use entity beans? I guess the right way is vendor dependant? Sure would be nice to have a JBoss performance guidance manual.

Sorry for the newbie ramblings.

Thanks for any insight.

[Marco DeFreitas]

Shoot! I see someone posted a similar question... sorry...

But a good book recommendation would still be appreciated.

[Pete Luellen]

I've been using "Mastering Enterprise JavaBeans" by Ed Roman. I got it with a 20% discount at Barnes & Noble. It's pretty well written I think but (as you mention) fairly generic. No jboss deployment descriptors. In fact, I think the downloadable source code assumes a weblogic deployment.

I've also been using "Java Tools for Extreme Programming" by Rick Hightower. Also good. Also generic. Nothing jboss specific.

I'm thinking of putting together a set of simple examples (with jboss deployment) on my web site. But that probably won't happen for a couple of weeks.

Good luck.

[Dain Sundstrom]

That is the way it is now, and it will change in a future release.

[Tim Fox]

As I mentioned in the other post.

I you add an insert trigger on your table, that changes nulls into "some special key" when inserted into your table, you can keep the benefits of referential integrity and still use jboss 3.0 until it gets implemented.

(This assumes your db supports triggers)

Tim

[Dain Sundstrom]

You will have to handle updates also, as on delete all fk fields are set null. This is required by the remove protocol in the EJB spec.

[leela]

I have foreign Key Type INTEGER , How to make it NULLABLE . do i have to alter table externally or do i have a way of giving CMR_FIELD_TYPE Nullable in jdbccmp-jboss.xml.

ur response is appreciated.

Thanks.
leela

[leela]

I have foreign Key Type INTEGER , How to make it NULLABLE . do i have to alter table externally or do i have a way of giving CMR_FIELD_TYPE Nullable in jdbccmp-jboss.xml.

ur response is appreciated.

Thanks.
leela

[leela]

How to make foreign Keys nullable.

[Dain Sundstrom]

Foreign keys are automatically nullable. What database are you using?

[leela]

mysql

[Dain Sundstrom]

Do you have already existing tables or are you allowing jboss to create the tables for you? Are you using foreign key constraints? If so try turning of the generation of foreign key constraints (maybe mySQL is marking the column as not null).

Have you tried executing the sql by hand using the mySQL query tool? What errors do you get from the tool?

[leela]

Yes , It worked. I turned off Foreign Key constraints in the default section of jbosscmp-jdbc.xml.

Thanks Dain.
Leela