Migration Issue with composite-id

space.ghost · **Joined:** Tue Mar 21, 2006 1:42 pm **Posts:** 14

Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 3.1.2

Mapping documents:

Code:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>

   <class name="com.imsweb.seerdms.shared.entity.Tiger2004" table="TIGER_2004" mutable="false">
      <composite-id>
          <key-property name="tigerLineId" column="TLID" type="java.lang.Long" length="10"/>
         <key-property name="extAddressDataRecordSequenceNumber" column="EAD_RTSQ" type="java.lang.Integer" length="3"/>
         <key-property name="shapePointRecordSequenceNumber" column="SP_RTSQ" type="java.lang.Integer" length="3"/>
      </composite-id>
      
        <!-- Extended Address Data components -->
      <property name="extLeftStartAddress" column="EAD_FRADDL" type="java.lang.String" length="11"/>
      <property name="extLeftEndAddress" column="EAD_TOADDL" type="java.lang.String" length="11"/>
...
</class>
</hibernate-mapping>

Name and version of the database you are using: Oracle 10.1

I am migrating our application from 2.1.8 to 3.1.2 and have ran into an issue involving composite identifiers and how they are handled between the two versions. The composite-id for the mapping above contains three properties in which nulls may possibly exist. This mapping works correctly with Hibernate 2.1.8, probably shouldn't, but does not work with Hibernate 3.1.2. With Hibernate 3.1.2, when one of the properties that makes up the primary key is null, the value returned from a query is null. I suggested to my supervisor that we recreate the table adding a column that is an artificial primary key, but he does not find that solution preferential. Does anyone know of a good solution for mapping a table that has a composite-id with pieces that may be null?

Thank you,
Mike Ringrose

P.S. Should ComponentType throw an exception when it encounters a composite-id with null parts? Seems like it should since it's masking an application error.

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Oracle allows parts of primary keys to be null? Or have you just not marked the columns as being part of the primary key? I'm not aware of any DB that allows that. Certainly the SQL standard doesn't allow it. Here's a brief quote from the SQL92 standard (there are newer standards, but not on my hard drive :)

SQL92 wrote:

a) If PRIMARY KEY or UNIQUE is specified, then the <column defi-
nition> for each column whose <column name> is in the <unique
column list> shall specify NOT NULL.

If you have to keep the current schema, you'll have to remove all potentially-null columns from the id. If that makes the combinations non-unique, then you'll have to overrule your supervisor and insist on adding a real (legal) primary key.

space.ghost · **Joined:** Tue Mar 21, 2006 1:42 pm **Posts:** 14

I should have stated that there is no primary key on the table, the person that defined the table, simply placed a UNIQUE constraint on the columns listed in the composite-id. So it looks like Oracle defies the SQL92 standard (shocking) in that you can define a UNIQUE constraint on, "a single field or combination of fields that uniquely defines a record. Some of the fields can contain null values as long as the combination of values is unique." I knew that we'd need to add a new primary key column, but I figured it doesn't hurt to ask. Thanks.

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Actually, that doesn't break the standard. There is no requirement for a table to have a primary key, it's just normal practice. The requirement is that all columns in columns defined as PRIMARY KEY also have NOT NULL defined. And because you've told hibernate to treat those columns as if they were the primary key, it's (reasonably) assuming that they have the PRIMARY KEY property set in the DB. Hibernate doesn't work well with tables with no primary key.