References

Foreign and logical#

orders.erdCopyOpen in playground

external table users "Accounts service" {
  id  bigint  pk
}

table orders "Customer orders" {
  id       bigint  pk
  user_id  bigint  ~> users  "Customer"
}

table order_items "Order lines" {
  id        bigint  pk
  order_id  bigint  -> orders.id
}

order_items.order_id has a FOREIGN KEY constraint in the database, so it uses -> and is drawn solid. orders.user_id points into another service, where no constraint can exist, so it uses ~> and is drawn dashed. A table owned by someone else is declared external: list only the columns you point at.

Which column#

-> orders.id names the target column. Leave the column out and the reference points at the target's primary key, which must then be a single column. The target must be a table or an external table in the same document.

One or many#

payments.erdCopyOpen in playground

table orders {
  id  bigint  pk
}

table payments "Payments" {
  id        bigint  pk
  order_id  bigint  uk  -> orders  "One payment per order"
}

A reference is one-to-many unless the referencing column is unique. When it has uk, or is its table's only primary key, each parent has at most one child, and the relation is one-to-one. In the diagram the referencing end has a crow's foot and N, or a bar and 1.

Must a parent exist?#

checkout.erdCopyOpen in playground

table users {
  id  bigint  pk
}

table payments {
  id        bigint   pk
  payer_id  bigint?  ~> users  "Empty for guest checkout"
}

A NOT NULL referencing column means every row has exactly one parent. Make it nullable with ? and the parent becomes optional: zero or one. The diagram shows NULL on that row, and the connector's referenced end has a bar, a ring and 0..1 where a NOT NULL reference has two bars and 1.

Composite references#

composite.erdCopyOpen in playground

table products {
  tenant_id  bigint  pk
  id         bigint  pk
}

table order_items {
  id          bigint  pk
  tenant_id   bigint
  product_id  bigint
  foreign(tenant_id, product_id) -> products(tenant_id, id) as fk_product
  index(tenant_id, product_id)
}

The two lists are ordered: tenant_id references tenant_id, and product_id references id. This is one constraint and one model relation. Both lists need at least two distinct columns and the same number of columns. ~> writes a composite logical reference. A target in another service uses foreign(a, b) ~> service.target(x, y).

The diagram draws each column pair. Selecting any member highlights every pair in the constraint. The details panel lists the constraint as one reference. A composite relation is one-to-one when its complete source set is a primary or unique key. It is optional when any source column is nullable.