Primary key constraints ?

prashantjain · **Posted:** Wed May 24, 2006 7:43 am

I want to have a composite-id where Its is collection of 5 keys :

A , B , C , D , E.

It is possible sometimes that E is null but then combination of A, B , C , D are unique.
And in case when E is not null then A , B , C , D , E are together a primary key.

Can I have a primary key combination like the one mentioned above ?

kgignatyev · **Posted:** Wed May 24, 2006 3:21 pm

All parts of the primary key have to be not-null. Postgres makes them that way and MySQL too
See also http://bugs.mysql.com/bug.php?id=390

prashantjain · **Posted:** Wed May 24, 2006 11:56 pm

I am using sql server ... though there is no specified Primary Key, in application it is assumed that a combination is a primary key.

kgignatyev, Would that work with Hibernate ??

kgignatyev · **Posted:** Thu May 25, 2006 1:41 am

Composite ID should do the trick although there are bugs in Hibernate related to composite id handling.
like this http://opensource.atlassian.com/project ... e/HHH-1774

prashantjain · **Posted:** Thu May 25, 2006 2:02 am

ok..
If we go according to the example on that link ...

then instead of the following query:

Code:

from Cobrador cobrador where cobrador = :cobrador

if we change it to:

Code:

from Cobrador cobrador where cobrador.empresa = :emp
and cobrador.matricula = :mat

then that should work.
Right now I am using latter form of query so might not cause problem.

Code:

<composite-id name="id" class="Key" mapped="false" unsaved-value="undefined">
  <key-property name="eId" column="A" type="java.lang.Long" /> 
  <key-property name="pId" column="B" type="java.lang.Integer" /> 
  <key-property name="tId" column="C" type="java.lang.Integer" /> 
  <key-property name="lut" column="D" type="java.util.Date" /> 
  <key-property name="parId" column="E" type="integer" /> 
  <key-property name="object" column="object_sys" type="integer" not-null="false" /> 
  </composite-id>

In case when object_sys has null value then column A,B,C,D,E can uniquely identify the row.
but if object_sys is not null then all column A,B,C,D,E,object_sys are needed to uniquely identify a row.

What would u say about this ?

kgignatyev · **Posted:** Thu May 25, 2006 2:12 am

As long as there are no duplicates are returned from DB for a given ID Hibernate will be happy.

For querying by ID I suggest the syntax:
from Cobrador c where c.id.eId = :eid
and c.id.pId = :pid
....