# Composite keys pattern

> Learn how to join two models on several columns at once

## What is a composite key?

A composite key is a join key made of more than one column. No single column is unique on its own, but the combination is.

For example, a shipment is identified by its order **and** the warehouse it ships from. An order can ship from several warehouses, so `order_id` alone repeats in `shipments`. The pair `(order_id, warehouse_id)` is unique.

<ReactFlowDiagram id="erd-composite-keys" height={240} fixed />

**Sample data:**

`order_lines`

| id | order_id | warehouse_id | qty |
|----|----------|--------------|-----|
| l1 | o1 | w1 | 2 |
| l2 | o1 | w2 | 1 |
| l3 | o2 | w1 | 4 |

`shipments`

| order_id | warehouse_id | shipped_on | carrier |
|----------|--------------|------------|---------|
| o1 | w1 | 2025-01-03 | DHL |
| o1 | w2 | 2025-01-05 | UPS |
| o2 | w1 | 2025-01-04 | DHL |

The question: **how many units did each carrier ship?**

## Solution: one `on {}` block per column

Declare a `many_to_one` relationship with one `on {}` block per column pair. A row matches only when every pair is equal.

### Step 1: Define models
First, define the underlying models for both tables to expose their dimensions and measures. This ensures that the fields are correctly typed and ready to be joined.

```aml title="order_lines.model.aml"
Model order_lines {
  type: 'table'
  table_name: 'ecommerce.order_lines'
  data_source_name: 'your_datasource_name'

  dimension id {
    type: 'text'
    primary_key: true
  }

  dimension order_id { type: 'text' }
  dimension warehouse_id { type: 'text' }
  dimension qty { type: 'number' }

  measure total_qty {
    label: 'Total Quantity'
    type: 'number'
    definition: @aql sum(order_lines.qty);;
  }
}
```

```aml title="shipments.model.aml"
Model shipments {
  type: 'table'
  table_name: 'ecommerce.shipments'
  data_source_name: 'your_datasource_name'

  dimension order_id { type: 'text' }
  dimension warehouse_id { type: 'text' }
  dimension shipped_on { type: 'date' }
  dimension carrier { type: 'text' }
}
```

### Step 2: Define the relationship
Next, define the relationship between the two models using the composite key columns. This tells Holistics how to join the tables when they are queried together.

```aml title="order_line_shipment.relationship.aml"
Relationship order_line_shipment {
  type: 'many_to_one'
  on { from: r(order_lines.order_id)     to: r(shipments.order_id) }
  on { from: r(order_lines.warehouse_id) to: r(shipments.warehouse_id) }
}
```

The `many_to_one` claim applies to the pair. Each order line matches at most one shipment, because `(order_id, warehouse_id)` is unique in `shipments`. For a three-column key, add a third `on {}` block.

### Step 3: Add it to a dataset
Then, add the models and relationship to your dataset to make them available for querying. This exposes the unified data structure to your business users.

```aml title="fulfillment.dataset.aml"
Dataset fulfillment {
  data_source_name: 'your_datasource_name'

  models: [order_lines, shipments]

  relationships: [
    relationship(order_line_shipment, true)
  ]
}
```

### Step 4: Query
Finally, run a query to see how Holistics joins the tables and aggregates the data. You can now explore the combined fields seamlessly.

```aml
explore {
  dimensions { carrier: shipments.carrier }
  measures { qty: sum(order_lines.qty) }
}
```

Holistics emits one join with both conditions:

```sql
LEFT JOIN shipments
  ON  order_lines.order_id     = shipments.order_id
  AND order_lines.warehouse_id = shipments.warehouse_id
```

**Result:**

| carrier | qty |
|---------|-----|
| DHL | 6 |
| UPS | 1 |

The total is 7, the same as the sum of `order_lines.qty`. Each order line is counted once.

## Key points

- **Declare every column of the key.** The `many_to_one` claim is only true over the full combination. Leave a column out and the join fans out.
- **Uniqueness is your claim.** Holistics does not check that the combination is unique on the `to` side. If it is not, numbers are wrong in the same way as any false `many_to_one`.
- **Works on every database.** The emitted SQL is a plain multi-column equality join.

## Additional resources

- [Back to overview](/docs/modeling-patterns)
- [Relationships overview](/docs/relationships)
- [AML Relationship reference](/reference/aml/relationship)
- [Fan-out issues](/docs/joins/fan-out-resolution)