Hi,

The EJB3 persistence draft spec (ejb-3_0-edr2-spec-persistence) says the following about the overlapping of primary and foriegn keys in section 2.1.4.

[quote]
NOTE: The mapping of associations should be defined so as to avoid overlapping primary and foreign keys. The mapping of databases that have existing overlapping primary and foreign keys can be achieved by mapping the respective columns twice, once with insertable=false and updatable=false (for the instance variables or properties that correspond to the application's view of the primary key).
[/quote]

It is not very clear as to what should be the behaviour in case of both the primary and foriegn keys being composite.

We have a scenario where we need to map a OneToOne relation. Every table in the database has composite primary keys which means that every table has one common field (CMP_COD) in its primary key.

So we have

TABLE TST_ADDRESS (
CMP_COD VARCHAR2 (6) NOT NULL,
ADDRESS_ID VARCHAR2 (6) NOT NULL,
ADDRESS VARCHAR2 (30) NOT NULL,
CONSTRAINT PK_TST_ADDRESS
PRIMARY KEY ( ADDRESS_ID, CMP_COD )
)

and

TABLE TST_EMPLOYEE (
CMP_COD VARCHAR2 (6) NOT NULL,
EMP_ID VARCHAR2 (6) NOT NULL,
EMP_NAM VARCHAR2 (30) NOT NULL,
ADDRESS_ID VARCHAR2 (6),
CONSTRAINT PK_TST_EMPLOYEE
PRIMARY KEY ( EMP_ID, CMP_COD )
)

The reason for having a CMP_COD for all tables is to enable data partitioning in the DB for each company(CMP) since the system will be hosting multiple companies.

We need to form a OneToOne relation between Employee and Address wherein the CMP_COD
needs to be shared between the primary key and the foriegn key. We are using Hibernate for the persistence layer. Can anyone tell how such a mapping can be achieved.

[code]
@Entity(access = AccessType.PROPERTY)
@Table(name = "TST_EMPLOYEE")
public class Employee {

private EmployeePk employeePk;

private String employeeName;

private Address address;

---------------------
---------------------

/**
* @return Returns the address.
*/
@OneToOne
@JoinColumns( {
@JoinColumn(name = "CMP_COD", referencedColumnName = "CMP_COD", insertable=false, updatable=false),
@JoinColumn(name = "ADDRESS_ID", referencedColumnName = "ADDRESS_ID") })
public Address getAddress() {
return address;
}

}
[/code]

This results in an Exception as below.

org.hibernate.AnnotationException: Mixing insertable and non insertable columns in a property is not allowed: test.uni.Employeeaddress
at org.hibernate.cfg.Ejb3Column.checkPropertyConsistency(Ejb3Column.java:238)
at org.hibernate.cfg.AnnotationBinder.bindManyToOne(AnnotationBinder.java:1254)
at org.hibernate.cfg.AnnotationBinder.bindOneToOne(AnnotationBinder.java:1333)
at org.hibernate.cfg.AnnotationBinder.processElementAnnotations(AnnotationBinder.java:757)
at org.hibernate.cfg.AnnotationBinder.processElementsOfAClass(AnnotationBinder.java:573)
at org.hibernate.cfg.AnnotationBinder.bindClass(AnnotationBinder.java:436)
at org.hibernate.cfg.AnnotationConfiguration.addAnnotatedClass(AnnotationConfiguration.java:94)

Hibernate does not allow me to share just one in a foriegn key that contains more than one. Why is this? Is there any other way to achieve the mapping?

Thanks,
Dilip

What if you try to set an address with the wrong company code? It will be inconsistent.

Thanks for the reply, Emmanuel. I got that. But take the case of an entity which has more than one OneToOne relations. Let's say, Employee which is related to Address, Project and Location. Now, the employee table will look something like ...

TABLE TST_EMPLOYEE (
CMP_COD VARCHAR2 (6) NOT NULL,
EMP_ID VARCHAR2 (6) NOT NULL,
EMP_NAM VARCHAR2 (30) NOT NULL,
ADDRESS_CMP_COD VARCHAR2 (6),
ADDRESS_ID VARCHAR2 (6),
PROJ_CMP_COD VARCHAR2 (6),
PROJ_ID VARCHAR2 (6),
LOC_CMP_COD VARCHAR2 (6),
LOC_ID VARCHAR2 (6),
CONSTRAINT PK_TST_EMPLOYEE
PRIMARY KEY ( EMP_ID, CMP_COD )
)

But the content of the columns CMP_COD, ADDRESS_CMP_COD, PROJ_CMP_COD and LOC_CMP_COD will all be the same and redundant if the data in the table is consistent.

I get your point that someone could set wrong data (like address with wrong company code) into the entity if we share all the CMP_CODs. But can't we check this at runtime in this case by introducing an additional condition into the WHERE clause of the query which checks whether the CMP_CODs are actually the same like ....

UPDATE TST_EMPLOYEE SET ADDRESS_ID =
(
SELECT A.ADDRESS_ID
FROM TST_UNI_ADDRESS A, TST_UNI_EMPLOYEE B
WHERE A.ADDRESS_ID = ?
AND A.ALN_COD = B.ALN_COD
)

or something like this.

I ask this because I guess this will be a problem common to all huge systems which need to be multi-hosted and need data partitioning. And replication of the IDs will take quite a bit of disk space when the number of rows in the table increases.

It will be nice if I could get your views on this.

Thanks,
Dilip.

I'd rather buy new disks and keep my data coherent. PKs don't take significant disk space usually.