Relationships and optionality

Choose the foreign-key side#

For orders.customer_id > customers.id, the left side contains the foreign key. Read it as many orders referencing one customer. Reversing the operator changes which endpoint is the dependent side; reversing only the names without changing the operator does not preserve the relationship.

Table customers {
  id integer [pk]
}
Table orders {
  id integer [pk]
  customer_id integer [not null]
}
Ref order_customer: orders.customer_id > customers.id
  [delete: restrict, update: cascade]

The diagram uses column-attached edges and crow’s-foot markers. Inspect the endpoint and its nullability together when reviewing required versus optional relationships.

Optional references#

DBML 9.x optional endpoint operators are supported. For example, a category may have no parent:

Table categories {
  id integer [pk]
  parent_id integer [null]
}
Ref category_parent: categories.parent_id ?> categories.id
  [delete: set null]

Keep the nullable column and delete behavior consistent. Setting a foreign key to null cannot work on a non-nullable column in the target database.

Composite references#

List columns in the same semantic order at both ends:

Ref order_customer:
  orders.(tenant_id, customer_id) > customers.(tenant_id, id)

Both endpoint lists must have the same length, and paired column types should match. The plugin reports arity and type mismatches; changing the order can still be logically wrong even when types happen to agree. Define the corresponding composite key on the referenced table.

One-to-one references#

For standalone one-to-one declarations, the right endpoint owns the foreign key. The sample uses auth.accounts.(tenant_id, id) -? auth.profiles.(tenant_id, account_id) with a unique profile key. Inspect both uniqueness and optionality before assuming an edge alone enforces one-to-one behavior.

Use Rename on a referenced column and review both endpoints in the usage preview. See Inspections and quick fixes for missing table and column exercises.