Support for Composite Primary Keys with @EmbeddedId

[vnayar]

Is it currently possible to have a multi-column primary key?

For example, imagine a table of passports, and each person can actually get several passports over the course of their life. Thus, the unique primary key for a passport might be (PassportId, IssueDate).

With Hibernate, this is often accomplished using an @Embeddable class, but annotating it with @EmbeddedId rather than just @Id.

https://www.baeldung.com/spring-jpa-embedded-method-parameters

A workaround to this problem (if one can add an additional column), is to make a string column that combines multiple other columns, and compute this column as a D @property, e.g.

class Passport {
  @Id
  @property string id() {
    return passportId ~ "::" ~ issueDate;
  }

  @Id
  @property void id(string id) {
    import std.string : split;
    string[] parts = id.split("::");
    enforce(parts.length == 2, "Invalid Passport.id value: " ~ id);
    this.passportId = parts[0];
    this.issueDate = parts[1];
  }

  string passportId;
  string issueDate;
}

[SingingBush]

I don't think Hibernate supports composite keys currently but I've never tried it. It would be a good addition.

[vnayar]

Just adding a note here that support for @JoinColumns should be added as well, so that automatic object references can be decoded. As it is, @EmbeddedId adds quite a bit of value when dealing with existing databases that use multi-column primary keys, but its value could be further enhanced.

Consider the following relationship that is not currently supported but could be. In this case, we have contracts with vendors, and each contract can have many invoices against it. Invoices have IDs, but each vendor creates their own IDs unaware of each other, and they might re-use the same invoice_id for different contracts, thus, only the (vendor_id, contract_id, invoice_id) is unique.

SQL schema:

CREATE TABLE invoice (
    vendor_id TEXT NOT NULL,
    contract_id TEXT NOT NULL,
    invoice_id TEXT NOT NULL,
    amount INTEGER NOT NULL);
ALTER TABLE invoice
  ADD CONSTRAINT invoice_pk PRIMARY KEY (vendor_id, contract_id, invoice_id);

CREATE TABLE contract (
  id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  vendor_id TEXT NOT NULL,
  contract_id TEXT NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,

In HibernateD, this could be represented as follows:

@Embeddable
class InvoicePk {
  string vendorId;
  string contractId;
  string invoiceId;
}

@Entity
class Invoice {
  @EmbeddedId 
  InvoicePk pk;

  int amount;
}

@Entity
class Contract {
  int id;
  string vendorId;
  string contractId;

  @OneToMany @JoinColumns([ JoinColumn("vendor_id"), JoinColumn("contract_id") ])
  Invoice[] invoices;
}

Reference: https://docs.oracle.com/javaee%2F7%2Fapi%2F%2F/javax/persistence/JoinColumns.html