Hi,

I have encounter a very simple problem on one-to-many mapping that associates two tables. But with a foreign key value in one table doesn't exists in the primary key value in another table.

I did search through the forum and I know for a good relational model, values of a foreign key should either exists in the primary table or it is assigned as a null value. But actually in my legency database I need to handle this invalid foreign key.

One of the table is a network event log table which contains hundreds of millions records. Each with an ip address assigned. There is another host table which consists of an ip address (key) and the corresponding host name. I just want to associates this two tables using Hibernate but I couldn't because the ip address in the event log table may not exists in the host table. The host table only have values for the ip interested by the user.

What I want is just to display the host names if I can find them in the host table, or else I display the ip address. But Hibernate gives an "No row with the given identifier exists" error if there is one foreign key missing in the host table.

Actually event I can change the database schema, I can't simply add another ip field to the event log which assign null value if there is no such ip in the host table, otherwise even a single record is added in the host table, system need to update hundreds of millions of records in the event log tables to preserve the integrity, which is very ineffiencient.

I am really frustrated.

Hibernate version:2.1.5

Mapping documents:

(Simplified)

<class
name="EventLog"
table="event_log"
>
<id
name="id"
type="long"
column="id"
>
<generator class="native" />
</id>
<property
name="ip"
type="java.lang.String"
column="ip"
/>
<property
name="port"
type="int"
column="port"
/>
<property
name="message"
type="java.lang.String"
column="message"
length="100"
/>
<property
name="recDate"
type="java.sql.Timestamp"
column="rec_date"
not-null="true"
length="23"
/>
<!-- associations -->
</set>
<many-to-one class="IpHost" name="host" column="ip"
insert="false" update="false" outer-join="true"/>
</class>

<class
name="IpHost"
table="ip_host"
>
<id
name="ip"
type="java.lang.String"
column="ip"
>
<generator class="assigned" />
</id>
<property
name="hostname"
type="java.lang.String"
column="hostname"
length="50"
/>
</class>

You will not be able to map this as an association. Essentially Hibernate assumes a certain level of good database modeling, including proper fk integrity.

You could, however, resolve this using a UserType.

thanks for the reply.

But I can't get the point how UserType can solve the problem. Would you mind refer me some links on how UserType can be use related to these kinds of integrity problems?

And in fact, I really need some level of associations between the two. Maybe I am over-simplified my situation. The hostname table is in fact not a standalone table. There are many other tables associates with it. For example, the host physical location tables, relationship tables between host, etc. I just want to navigate these things as well from event log table to the host structures if they exists.