Technology » JPA, Hibernate, and Co. » Relationships to Non-Primary Key Columns

Relationships to Non-Primary Key Columns

Table of Contents

The JPA 1.0 specification has never supported relationships to non-primary key columns. Section 11.1.21 of the current specification describes the @JoinColumn annotation and in that section there is a paragraph that reads:

Support for referenced columns that are not primary key columns of the referenced table is optional. Applications that use such mappings will not be portable.

Note, that even though the JPA has never explicitly supported relationships to non-primary key columns, it doesn't forbid them. So, if any of these worked for you it was just an optional feature that your JPA provider has implemented.

To find out exactly which non-primary key relationship constellations are supported by each JPA provider, I created a test case in which there is exactly one combination of each relationship type:

  • Single-column non-identifying relationship
  • Single-column identifying relationship
  • Multi-column non-identifying relationship
  • Multi-column identifying relationship

If you have never heard of identifying and non-identifying relationships, you can find out here. For each of the above identifying relationship types it was necessary to try the four basic JPA composite key mapping variants:

  • JPA 1.0 @IdClass
  • JPA 1.0 @EmbeddedId
  • JPA 2.0 @IdClass
  • JPA 2.0 @EmbeddedId

Note, that only identifying relationships differ in the implementation of composite primary keys, because only these foreign key columns that are also part of the primary key are included in JPA composite key classes. Thus, there are eight possible mapping combinations for the identifying relationship types, four for single-column and four for multi-column identifying relationships. For non-identifying relationships the columns will "pass by" the composite primary key mappings.

The two non-identifying and the eight identifying relationships resulted in exactly ten basically different relationship mapping constellations. I tried each one with Hibernate and EclipseLink to determine if the JPA provider is able to map them. Note again, that I'm still only talking about relationships to non-primary key columns. You can add an invisible "to non-primary key columns" after each occurrence of "relationship" if that helps.

Test Cases

I created the same (somewhat theoretical) test case for non-identifying and identifying relationships. The dashed lines below represent non-identifying relationships, of which there are two, with only two ways to map them in JPA. The solid lines represent identifying relationships, of which there are also two, but they can be mapped in four different ways (JPA 1.0 @IdClass, JPA 1.0 @EmbeddedId, JPA 2.0 @IdClass, and JPA 2.0 @EmbeddedId), resulting in eight distinct identifying relationship mappings. The total number of mappings for each JPA provider is ten - as seen in the mapping matrices below.

As you can see from the designs, the Countries and States tables are identical in both test cases. There's furthermore a UNIQUE constraint on Countries (iso_code) and on States (country_code, iso_code) which are each referenced by the Zips and Cities tables. (You'd better not ask yourself if this design makes sense or not. For German cities the design works fine, because there are no cities with the same name in a state. Just take it for granted!)

Here's the design using non-identifying relationships (dashed lines):

Test Case for Non-Identifying Relationships (to Non-Primary Key Columns)
Test Case for Non-Identifying Relationships (to Non-Primary Key Columns)

Here's the design using identifying relationships, to which four different composite primary key mappings are applied:

Test Case for Identifying Relationships (to Non-Primary Key Columns)
Test Case for Identifying Relationships (to Non-Primary Key Columns)

Note the different mappings only affect the entity classes referencing the others, here Zip and City. The only exception is the identifying relationship to States in the lower right "corner": because the CityId composite key class nests other multi-column key classes in the case of the JPA 2.0 mappings, I had to create a StateId composite key class, even though it doesn't represent the primary but the alternative key (country_code, iso_code).

The Test Program

Country co = em.find(Country.class, 1);
System.out.println("Loaded country = " + co);
		
//Zip zi = em.find(Zip.class, 4459);
Zip zi = em.find(Zip.class, new ZipId("DE", "64846"));
System.out.println("Loaded zip = " + zi);
		
State st = em.find(State.class, 247);
System.out.println("Loaded state = " + st);
		
//City ci = em.find(City.class, 8649);
City ci = em.find(City.class, new CityId("DE", "HE", "Dieburg"));
System.out.println("Loaded city = " + ci);

The above test program needs to be adjusted for Zip and City depending on which test case (non-identifying vs. identifying relationships) is used. The identifying relationship case always involves Zip and City composite primary key classes in em.find(...).

Concrete Mappings

This section contains all the necessary mappings used in the test cases. The is just for reference. You can safely skip this section entirely if you trust me having made no mapping errors.

Single-Column Non-Identifying Relationships

@Entity
@Table(name = "Countries")
public class Country implements Serializable
{
	@Id
	@Column(name = "id")
	private Integer id;

	@Column(name = "iso_code")
	private String isoCode;

	...
}
@Entity
@Table(name = "Zips")
public class Zip implements Serializable
{
	@Id
	@Column(name = "id")
	private Integer id;

	@Column(name = "code")
	private String code;

	@ManyToOne
	@JoinColumn(name = "country_code", referencedColumnName = "iso_code")
	private Country country;

	...
}

Multi-Column Non-Identifying Relationships

@Entity
@Table(name = "States")
public class State implements Serializable
{
	@Id
	@Column(name = "id")
	private Integer id;

	@Column(name = "country_code")
	private String countryCode;

	@Column(name = "iso_code")
	private String isoCode;

	...
}
@Entity
@Table(name = "Cities")
public class City implements Serializable
{
	@Id
	@Column(name = "id")
	private Integer id;

	@Column(name = "name")
	private String name;

	@ManyToOne
	@JoinColumns(value = {@JoinColumn(name = "country_code", referencedColumnName = "country_code"), @JoinColumn(name = "state_code", referencedColumnName = "iso_code")})
	private State state;

	...
}

Single-Column Identifying Relationships, JPA 1.0 @IdClass

@Entity
@Table(name = "Countries")
public class Country implements Serializable
{
	@Id
	@Column(name = "id")
	private Integer id;

	@Column(name = "iso_code")
	private String isoCode;

	...
}
@Entity
@Table(name = "Zips")
@IdClass(value = ZipId.class)
public class Zip implements Serializable
{
	@Id
	@Column(name = "country_code", insertable = false, updatable = false)
	private String countryCode;

	@Id
	@Column(name = "code")
	private String code;

	@ManyToOne
	@JoinColumn(name = "country_code", referencedColumnName = "iso_code")
	private Country country;

	...
}
public class ZipId implements Serializable
{
	private String countryCode;

	private String code;

	...
}

Single-Column Identifying Relationships, JPA 1.0 @EmbeddedId

@Entity
@Table(name = "Countries")
public class Country implements Serializable
{
	@Id
	@Column(name = "id")
	private Integer id;

	@Column(name = "iso_code")
	private String isoCode;

	...
}
@Entity
@Table(name = "Zips")
public class Zip implements Serializable
{
	@EmbeddedId
	private ZipId embeddedId;

	@ManyToOne
	@JoinColumn(name = "country_code", referencedColumnName = "iso_code")
	private Country country;

	...
}
@Embeddable
public class ZipId implements Serializable
{
	@Column(name = "country_code", insertable = false, updatable = false)
	private String countryCode;

	@Column(name = "code")
	private String code;

	...
}

Single-Column Identifying Relationships, JPA 2.0 @IdClass

@Entity
@Table(name = "Countries")
public class Country implements Serializable
{
	@Id
	@Column(name = "id")
	private Integer id;

	@Column(name = "iso_code")
	private String isoCode;

	...
}
@Entity
@Table(name = "Zips")
@IdClass(value = ZipId.class)
public class Zip implements Serializable
{
	@Id
	@Column(name = "code")
	private String code;

	@Id
	@ManyToOne
	@JoinColumn(name = "country_code", referencedColumnName = "iso_code")
	private Country country;

	...
}
public class ZipId implements Serializable
{
	private String country;

	private String code;

	...
}

Single-Column Identifying Relationships, JPA 2.0 @EmbeddedId

@Entity
@Table(name = "Countries")
public class Country implements Serializable
{
	@Id
	@Column(name = "id")
	private Integer id;

	@Column(name = "iso_code")
	private String isoCode;

	...
}
@Entity
@Table(name = "Zips")
public class Zip implements Serializable
{
	@EmbeddedId
	private ZipId embeddedId;

	@MapsId(value = "countryCode")
	@ManyToOne
	@JoinColumn(name = "country_code", referencedColumnName = "iso_code")
	private Country country;

	...
}
@Embeddable
public class ZipId implements Serializable
{
	@Column(name = "country_code", insertable = false, updatable = false)
	private String countryCode;

	@Column(name = "code")
	private String code;

	...
}

Multi-Column Identifying Relationships, JPA 1.0 @IdClass

@Entity
@Table(name = "States")
public class State implements Serializable
{
	@Id
	@Column(name = "id")
	private Integer id;

	@Column(name = "country_code")
	private String countryCode;

	@Column(name = "iso_code")
	private String isoCode;

	...
}
@Entity
@Table(name = "Cities")
@IdClass(value = CityId.class)
public class City implements Serializable
{
	@Id
	@Column(name = "country_code", insertable = false, updatable = false)
	private String countryCode;

	@Id
	@Column(name = "state_code", insertable = false, updatable = false)
	private String stateCode;

	@Id
	@Column(name = "name")
	private String name;

	@ManyToOne
	@JoinColumns(value = {@JoinColumn(name = "country_code", referencedColumnName = "country_code"), @JoinColumn(name = "state_code", referencedColumnName = "iso_code")})
	private State state;

	...
}
public class CityId implements Serializable
{
	private String countryCode;

	private String stateCode;

	private String name;

	...
}

Multi-Column Identifying Relationships, JPA 1.0 @EmbeddedId

@Entity
@Table(name = "States")
public class State implements Serializable
{
	@Id
	@Column(name = "id")
	private Integer id;

	@Column(name = "country_code")
	private String countryCode;

	@Column(name = "iso_code")
	private String isoCode;

	...
}
@Entity
@Table(name = "Cities")
public class City implements Serializable
{
	@EmbeddedId
	private CityId embeddedId;

	@ManyToOne
	@JoinColumns(value = {@JoinColumn(name = "country_code", referencedColumnName = "country_code"), @JoinColumn(name = "state_code", referencedColumnName = "iso_code")})
	private State state;

	...
}
@Embeddable
public class CityId implements Serializable
{
	@Column(name = "country_code", insertable = false, updatable = false)
	private String countryCode;

	@Column(name = "state_code", insertable = false, updatable = false)
	private String stateCode;

	@Column(name = "name")
	private String name;

	...
}

Multi-Column Identifying Relationships, JPA 2.0 @IdClass (experimental)

@Entity
@Table(name = "States")
public class State implements Serializable
{
	@Id
	@Column(name = "id")
	private Integer id;

	@Column(name = "country_code")
	private String countryCode;

	@Column(name = "iso_code")
	private String isoCode;

	...
}
public class StateId implements Serializable
{
	private String countryCode;

	private String isoCode;

	...
}
@Entity
@Table(name = "Cities")
@IdClass(value = CityId.class)
public class City implements Serializable
{
	@Id
	@Column(name = "name")
	private String name;

	@Id
	@ManyToOne
	@JoinColumns(value = {@JoinColumn(name = "country_code", referencedColumnName = "country_code"), @JoinColumn(name = "state_code", referencedColumnName = "iso_code")})
	private State state;

	...
}
public class CityId implements Serializable
{
	private StateId state;

	private String name;

	...
}

Multi-Column Identifying Relationships, JPA 2.0 @EmbeddedId (experimental)

@Entity
@Table(name = "States")
public class State implements Serializable
{
	@Id
	@Column(name = "id")
	private Integer id;

	@Column(name = "country_code")
	private String countryCode;

	@Column(name = "iso_code")
	private String isoCode;

	...
}
@Embeddable
public class StateId implements Serializable
{
	@Column(name = "country_code")
	private String countryCode;

	@Column(name = "iso_code")
	private String isoCode;

	...
}
@Entity
@Table(name = "Cities")
public class City implements Serializable
{
	@EmbeddedId
	private CityId embeddedId;

	@MapsId(value = "stateId")
	@ManyToOne
	@JoinColumns(value = {@JoinColumn(name = "country_code", referencedColumnName = "country_code"), @JoinColumn(name = "state_code", referencedColumnName = "iso_code")})
	private State state;

	...
}
@Embeddable
public class CityId implements Serializable
{
	@Embedded
	private StateId stateId;

	@Column(name = "name")
	private String name;

	...
}

Mapping Matrices

In a rather lengthy procedure of trial and error I figured out which relationship combinations are OK and which ones are not OK using Hibernate 3.6.0 and EclipseLink 2.2.0. You can view the results below.

Hibernate 3.6.0

  Non-Identifying Relationships
Single-Column
Multi-Column

  Identifying Relationships
JPA 1.0 JPA 2.0
Single-Column @IdClass
@EmbeddedId
Multi-Column @IdClass (✗)
@EmbeddedId (✗)

EclipseLink 2.2.0

  Non-Identifying Relationships
Single-Column
Multi-Column

  Identifying Relationships
JPA 1.0 JPA 2.0
Single-Column @IdClass
@EmbeddedId
Multi-Column @IdClass (✗)
@EmbeddedId (✗)

() = experimental:

Multi-column identifying relationships require a nested composite key class of the referenced entity, which has to be derived from non-primary key columns, here an alternative key (UNIQUE, NOT NULL). This can't be expected to work and is considered experimental. This further means such mappings can't be used transitively, making these mappings virtually impossible to map with any JPA provider.

Interpretation of Results

Hibernate and EclipseLink do a decent job of mapping non-primary key relationships overall, as at least both JPA providers get the JPA 1.0 mappings right. However, as you can see from the mapping matrices, Hibernate and EclipseLink both have problems mapping (identifying) relationships to non-primary key columns as derived identifiers (JPA 2.0 @IdClass and @EmbeddedId). Only Hibernate manages to map single-column identifying relationships to non-primary key columns using JPA 2.0 @EmbeddedId syntax.

EclipseLink single-column, identifying relationships mapped as JPA 2.0 @EmbeddedId don't work and I suspect the tool to need an enhancement here (link ballot x). In my opinion, both JPA providers should support at least single-column identifying relationships in derived identities, here both JPA 2.0 @IdClass and @EmbeddedId syntaxes. After all, the JPA 1.0 mappings work, so why shouldn't the JPA 2.0 code work, too?

I cling to remember situations where I ran into problems with transitive multi-column relationships, identifying and non-identifying. I'll have to re-check and maybe compile another test case for that matter. Stay tuned.

Epilog on JPA 2.0

In my opinion the JPA should allow relationships to non-primary key columns in the future. I often find myself modeling relationships, especially when using inheritance and another table, where I'd like to return to natural key columns in the primary key without having to resort to the meaningless ID defined by the inheritance root table.

However, adding support for non-primary key relationships isn't trivial. There are a number of questions that remain to be solved, for example how to ensure identity. To me a restriction to allow non-primary key relationships to non-null, unique columns seems logical, but I definitely don't know the details here. One consequence of allowing relationships to alternative keys of course would be having to support more than one composite key class (ID class). It's probably a lot more complicated than that though.