Composed keys

Composed candidate keys are often business keys. The underlying logic defines which objects are considered to be identical based on their values.

As an example, we consider a company having several departments. Regarding projects he following business rules shall apply:

Figure 945. Business rules for projects

  1. Each department must have a unique name.

  2. A project's name must be unique within the set of all projects belonging to the same department.

  3. A project must be assigned to exactly one department.


Right now we defer considerations of the n:1 relationship between departments and projects to a later chapter. Instead we focus just on project instances and represent departments just by their integer id values which will later become foreign keys.

In addition each project receives a unique integer id value as well. This is in accordance with the “best practice” rule of defining a surrogate key ❷ to be used as (primary) object identifier. This immutable key will then become the target in foreign key definitions:

Java 
package hibintro.v6;
   ...
@Entity
@Table(uniqueConstraints={@UniqueConstraint(columnNames={"name", "department"})}) ❶
public class Project {

  int id;
  @Id ❷
  public int getId() { return id; }
  protected void setId(int id) { this.id = id; }

  String name;
  @Column(nullable=false)
  public String getName() { return name; }
  public void setName(String name) { this.name = name; }

  int department;
  @Column(nullable=false)
  public int getDepartment() { return department; }
  public void setDepartment(int department) { this.department = department; }
  ...
}
Sql 
CREATE TABLE Project (
  id int(11) NOT NULL PRIMARY KEY ❷,
  department int(11) NOT NULL,
  name varchar(255) NOT NULL,
  UNIQUE KEY name (name,department) ❶
)

❷

Defining the surrogate primary key.

❶

Defining a business key composed of a project's name and department number. This implements our second business rule in Figure 945, “Business rules for projects ”.

exercise No. 18

JPA id getter visibility.

Q:

The setter void setId(int)in hibintro.v6.Project has protected write access. Explain this choice.

A:

From an application developer's point of view the setter should be absent: The id property is immutable and should not be accessed at all.

When loading an instance from a database a persistence provider however has to set its value. Hibernate uses the reflection-API to override the restriction being imposed by the protected modifier. So why not declare it private? Doing so may cause our IDE to flag a warning about an unused private method.

So choosing protected is a compromise: An application developer cannot modify the property (unless deriving a class) and our persistence provider can still set its value to the database's primary key attribute value.

Prev  Up  Next
Defining keys  Home  Indexes (non-unique)