I have a couple of lookup-tables with composite primary keys, and I've modelled them as composite IDs. I'll give an example:

Sub Table 1 (primary key is sub_1 and language_id)

sub_1
language_id


Sub Table 2 (primary key is sub_2, language_id)

sub_2
language_id


Main Table

main_1
language_id
sub_1
sub_2


So main table has a reference to both of the sub-tables (and the foreign keys are nullable).

Lets say that there is a row in Main Table where Sub Table 1 has a key but Sub Table 2 does not. In this situation, language_id and sub_1 will be set, but sub_2 will be null.

If I ask for the Sub Table 2, Hibernate still tries to do a lookup, even though sub_2 is null. And if it can't find it, it throws an UnresolvableObjectException rather than returning null. Should I just catch the exception in these cases? Unfortunately, I can't check sub_1 or sub_2 without retrieving the Sub Table 1 and Sub Table 2 table

Is there a better way of handling this?

Thanks in advance,
David.

There was a post some times ago on that subject, but I can't remember which one.

Yes, it was here:

http://forum.hibernate.org/viewtopic.ph ... texception

But no answer was ultimately given; the dialog digressed into the semantics of composite primary keys (and database behavior when one was null) amongst different databases.

Someone made the comment that, "SQLServer thinks that if one of the fk column is null the fk is null. Don' know how other db handle that but my dba told me that this is the most common case."

His DBA is wrong - both MySQL and Oracle, arguably the two most popular databases, both allow null in one or more of the components of a composite primary key if at least one of the components is not-null.

I suspect that handling the exception is all that can be done.

Yes, it was here:

http://forum.hibernate.org/viewtopic.php?t=926852&highlight=unresolvableobjectexception

But no answer was ultimately given; the dialog digressed into the semantics of composite primary keys (and database behavior when one was null) amongst different databases.

Someone made the comment that, "SQLServer thinks that if one of the fk column is null the fk is null. Don' know how other db handle that but my dba told me that this is the most common case."

His DBA is wrong - both MySQL and Oracle, arguably the two most popular databases, both allow null in one or more of the components of a composite primary key if at least one of the components is not-null.

I suspect that handling the exception is all that can be done.