Bug 345839

Description Karsten Wutzke 2011-05-15 07:07:44 EDT

Build Identifier: EclipseLink 2.3.0 M7 (R9282)

Here's the design:

http://www.kawoolutions.com/media/countries-zips.png

Here's the DDL used (HSQLDB ISO/ANSI):

CREATE TABLE Countries
(
  id INTEGER NOT NULL,
  iso_code CHAR(2) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE (iso_code)
);

CREATE TABLE Zips
(
  country_code CHAR(2) NOT NULL,
  code VARCHAR(10) NOT NULL,
  PRIMARY KEY (country_code, code),
  CONSTRAINT zips_countries_fk
    FOREIGN KEY (country_code)
    REFERENCES Countries (iso_code)
    ON DELETE NO ACTION
    ON UPDATE CASCADE
);

Here are the mappings:

@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;

	...
}

Problem:

The @MapsId represents a single-column relationship to another entity's non-primary key column (JPA 2.0). The JPA (1.0 + 2.0) only supports relationships to primary key columns in derived identies.

However, also due to the requirement of inheritance usually forcing a common ID onto the sub entities, other entities referencing the (sub) entities that use surrogate/artificial IDs can't define primary keys constructed from (single- or multi-column) non-primary key columns. The current policy - essentially a weakness of JPA - forces developers to continue to use the meaningless ID, even though a good natural key exists. This really limits DB design.

There should be a mechanism to "return" to the non-primary key attributes for referencing entities. EL should allow relationships to non-PK columns in derived identifiers - @IdClass and @EmbeddedId implementations alike.

Analysis:

Note that referencing multiple non-primary key columns would mean a reference to an ID class of an alternative/natural key that the class doesn't necessarily have (JPA 2.0 may nest ID classes). As the referenced entity class will already have either an @Id or an @IdClass/@EmbeddedId representing the PK, multi-column relationships to alternative keys would require another ID class, which may collide with existing ID classes.

The restriction to be put upon here would probably be to only allow references to multiple non-PK columns in entities that have a single-column PK. I don't know the details here, or whether that's possible at all.

Maybe this has to be limited to single-column relationships to non-PK columns.

Deeper analsysis is definitely required.

Reproducible: Always

Steps to Reproduce:
1. See test case
2.
3.