Hibernate Issue : Foreign key must have same number of columns as referenced primary key

Question

Goal : I want to have importJobId in ImportJob as foreign key for id of allocation table, such that when we have importJobId then and then only we can have id in allocation as without Job there cannot be any allocations.

ImportJob table has composite primary key as [ORGID,IMPORTJOBTYPE] and am trying to get create foreign key relationship in hibernate using

  <id name="id"
        column="ID">
        <generator class="native"/>
    </id>
    <many-to-one name="importjobid"
                 class="com.delta.pdo.admin.ImportJob"
                 cascade="save-update"/>

in Allocation.hbm.xml which is not working out and am getting error message as:

Foreign key (FKB29B5F7366007086:ALLOCATIONS [importjobid])) 
must have same number of columns as the 
referenced primary key (IMPORTJOBMANAGMENT [ORGID,IMPORTJOBTYPE])

Here are my ImportJob.hbm.xml file

    <?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
    <class name="com.delta.pdo.admin.ImportJob" table="IMPORTJOB" lazy="false">
        <!-- we don't cache this since the commissions code is too screwed up to work with -->
        <composite-id>
            <key-property name="orgId" type="long" column="ORGID"/>
            <key-property name="importJobType" type="java.lang.String" column="IMPORTJOBTYPE"/>
        </composite-id>

        <!-- Make sure importjobid is not-null='true '-->
        <property name="importjobid" type="long" column="IMPORTJOBID" />
        <property name="allocations" type="boolean" column="ALLOCATIONS" />
    </class>
</hibernate-mapping>

Here are the bean classes for reference:

public class AllocationBean extends WorkbenchBeanBase
{
    private static final Logger log = Logger.getLogger(AllocationBean.class);
    private Float allocations;
    private String importJobType;
    private long id;
    private long orgId;
}

public class ImportJobManagment implements Serializable
{
    private long importjobid;
    private long orgId;
    private String importJobType;
    private boolean allocations = false;
}

I have removed getter/setter for sake of simplicity.

Update : 1 The way it is setup right now, I have id column in one table that have foreign key reference to composite key of orgId and importJobType, am not sure if we can do this and have single column foreign keyed to composite key of another table but that's my use case.

Update : 2

Thanks for awesome details, this would certainly enchance my knowledge of foreign key implementations but my final goal is to one to one mapping between two tables, where table A has composite key for identifying unique row in that table and in table B, I want to have primary key which would have foreign key reference to table A such that if we have entry in table A then same jobId entry should be in Table B, now I get your point that we cannot have single column primary key in table B that would reference composite key in table A.

so basically i want to have one to one mapping between tables where Table A has composite primary key and table B has single column primary key using hibernate, which is ofcourse am getting the mentioned error and so now i am going to create composite keys in table B also and now make foreign key reference to table A, I will verify and update my question with my findings later, again thanks for detail inputs.

Answer 1

The error speaks for itself, in order to reference a composite primary key, you need a composite foreign key. (The composite primary key states that you need an unique combination of 2 fields to make a key - then you can't possibly reference a unique key with just 1 column.)

As to how this is achieved by using xml mapping files i'm not sure, most people use annotations these days..

As for your Java classes, i'm assuming that ImportJobManagement holds an ImportJob, so then the class should not reference the id, but the object itself, like this:

public class ImportJobManagment implements Serializable {
    private ImportJob importJob;
    ...
}

The Java classes should just reference the other class, and not the members of the composite key - it is up to the mappings to map from the composite key to the Java member variable.

Answer to update:

The short answer is no, you can't. The way a foreign key works, it lets you reference a specific row in a table. And, in order to be sure to reference a specific row, you need an identity, something that would describe only one row, and no other. In SQL there is a construct for achieving this, namely the unique key. By stating that a column (or a composite of columns) is unique, you know that its/their combined value will be unique, there would be a maximum of 1 row in the entire table with this value, anything else would be a constraint violation.

So, a foreign key refers to either a single unique-constrained column, or a composite unique-key spanning multiple columns. Since all tables has a unique key already, the primary key (which is always unique), it is common to use this for foreign key references, but any unqiue column would work.

The easiest case is when we want to reference a table with a single-column unique key. Two simple tables A, which holds a single column 'id', and B, which holds an 'id' column, but also another column, a_id, which has a foreign key to the 'id' column of A. An instance of this situation could be this:

A:
| id | 
|----|
|  1 |
|  2 |
|  3 |

B:
| id | a_id |
| 2  |  3   |
| 3  |  1   |

Here, each row in B reference a row in A. Its a direct reference, the value in a_id in table B corresponds directly to the value in A' 'id' column. So the B with id 2 reference the A with id 3, and so on.

Now lets look at how to reference a table with a composite unique key. Lets keep our example, but now A has another column 'sec_id', which together with 'id' compose a composite primary key.

A:
| id | sec_id |
|----|--------|
| 1  |   3    |
| 3  |   1    |
| 3  |   7    |

B:
| id | a_id |
|----|------|
| 2  |  3   |

In this situation, we have a problem in B. Since a foreign key must reference a single row in the table that its referencing, this clearly does not work. Which row in A does the value '3' represent? The sec_id in the first row? The id in the second or third (but in that case, which one?)? The answer is of course neither, there is not enough information in B to reference a single row in A, so SQL just won't have it. Adding such a foreign key is thus not allowed.

In order for B to reference A, it will require both a reference to A's 'id'-column, and A's 'sec_id' column, because a single row in A is identified by its unique combination of ('id', 'sec_id') pairs. So with B looking like this:

| id | a_id | a_sec_id |
|----|------|----------|
| 1  |  1   |     3    |
| 2  |  3   |     1    |
| 3  |  3   |     7    |

Now, B holds enough information to reference a single row in A, and as the data shows, it does.

Update again:

I'm currently reading for JPA certification, and have reached the chapter on composite key mappings. In order to map a composite primary key, you need a primary key class which maps the attributes of your key. There are two ways of doing it, one where the key attribute must also be mapped in the entity itself, and one where it's used as an embedded key.

I'll provide the code examples, they pretty speak for themselves (it's using annotations, you really should do that as well.)

The first example is the base example, with a regular id-class (not embedded). Here, we have an Employee entity where the primary key is composed of an integer id and a country (two Employees can have the same id if in different countries).

@Entity
@IdClass(EmployeeId.class)
public class Employee {
    @Id private String country
    @Id
    @Column(name = "EMP_ID")
    private int id;
    private String name;
    ...
}

public class EmployeeId implements Serializable {
    private String country;
    private int id;

    public EmployeeId() { }
    public EmployeeId(final String country, final int id) {
        this.country = country;
        this.id = id;
    }

    //getters for the properties

    public boolean equals(final Object other) {
    //must be implemented
    }

    public int hashCode() {
    //must be implemented
    }
}

Note:

• The @IdClass-annotation on the class.
• Both the attributes of the id-class must also be defined in the entity
• The id-class must implement equals and hashcode

The other way this can be done is through an embedded id-class:

@Entity
public class Employee {
    @EmbeddedId
    private EmployeeId id;
    private String name;

    public Employee(final String country, final int id) {
        this.id = new EmployeeId(country, id);
    }

    public String getCountry() {
        return id.getCountry();
    }
}   

@Embeddable
public class EmployeeId {
   private String country;
   @Column(name = "EMP_ID")
   private int id;

   //constructor + getters + equals +hashCode
}

Note:

• No need to define the id-attributes in the entity
• To get attributes of the id-class from the entity, you need to get them from id-class

I like the latter better because its more compact and doesn't contain duplication, but then again I don't know how using the two compares..