Relations

Terminology

FK - Short for Foreign Key.

The example

In this topic we shall have two tables: cars and wheels, where the wheels has a FK column "car_id" referring the right car.

PL relation scenarios

In PL we distinguish between two scenarios. The features of each scenario shall be described on the next chapters.

Secondary Tables (One-to-one Relation)

Suppose a car could only have a single wheel. In this case, PL allows you to define all the wheel fields directly on the car entity so that you won't need to care anymore for which field belongs to which table.

There is only one entity in this scenario.

Parent and Child Entities (One-to-many Relation)

If a car could have many wheels, we cannot have all the fields "flatten" on a single entity but rather we shall have two different entities. The car shall be called "parent" and the wheel shall be its "child".

Declaring the Relations (`createFKField`)

In SQL databases, a table could have a foreign key to another table. This FK could be one or more fields which together uniquely identifies the other guy.
PL does not require the schema to have real FK. But we do need to declare them using JOOQ.

public class Cars extends AbstractDataTable<Cars> {
  public static final Cars TABLE = new Cars("cars");
  //
  // car fields
  // 
  public final TableField<Record, Integer> id = createPKField("id", SQLDataType.INTEGER.identity(true));
}

public class Wheels extends AbstractDataTable<Wheels> {
  public static final Wheels TABLE = new Cars("wheels");
  //
  // wheel fields
  // 
  public final TableField<Record, Integer> car_id = createFKField("car_id", Cars.TABLE.id);
}