# Model related tables

Keep customers, orders and the items on each order in their own tables, and link them with foreign keys. ADoor keeps them in real Postgres, refuses a link to a row that doesn't exist, and lets every assistant you connect join the tables with SQL.

## How a link works

Every table has an `id` field. Unless you define it yourself, it is a UUID that ADoor fills in. A reference is a foreign key from a field in one table to the `id` of another, so `orders.customer_id` points at `customers.id`.

- A row can only point at a row that exists. Anything else is refused, and nothing changes.
- A row that another row still points at can't be deleted. A link never deletes rows for you: change or clear it first.
- A link field added with the reference can be empty, so an order can wait for its customer.
- The two tables can be in different spaces of the same workspace.

## Add a link

Ask in plain words, such as “Link each order to its customer.” The assistant calls `add_reference`:

```json
{
  "table": "orders",
  "column": "customer_id",
  "targetTable": "customers",
  "createColumn": true
}
```

`createColumn` adds `customer_id` as a new, empty field of the same type as `customers.id`. Leave it out to link a field you already have. Its type must match the `id` it points at, and every value already in it must point at a row that exists, or nothing changes.

In ADoor, open the space and choose Add reference from Space actions. Over the API the same operation is `references.create`.

Adding a link needs alter access on both tables. `CREATE TABLE` and `ALTER TABLE` don't accept `REFERENCES`, so every link is made with `add_reference`, `references.create` or the app.

## A worked example

Three tables, each created in its own `execute` call, since a schema change can't share a transaction with another statement:

```sql
CREATE TABLE customers (name text NOT NULL, email text)
CREATE TABLE orders (ordered_on date, status text)
CREATE TABLE order_items (product text, quantity integer, unit_price numeric(10,2))
```

Two links, one `add_reference` call each:

```json
{ "table": "orders", "column": "customer_id", "targetTable": "customers", "createColumn": true }

{ "table": "order_items", "column": "order_id", "targetTable": "orders", "createColumn": true }
```

Then the rows. `RETURNING id` gives back the new row's `id`, which the next insert passes as `$1`:

```sql
INSERT INTO customers (name, email) VALUES ('Northwind Cafe', 'orders@northwind.example') RETURNING id
INSERT INTO orders (customer_id, ordered_on, status) VALUES ($1, '2026-10-27', 'open') RETURNING id
INSERT INTO order_items (order_id, product, quantity, unit_price) VALUES ($1, 'Oat milk, 1 L', 24, 2.10), ($1, 'Espresso beans, 1 kg', 6, 18.50)
```

## When a link points at nothing

An order for a customer that doesn't exist:

```sql
INSERT INTO orders (customer_id, ordered_on, status) VALUES ('00000000-0000-4000-8000-000000000000', '2026-10-28', 'open')
```

is refused with `REFERENCE_CONFLICT`, and no row is written:

```text
This change conflicts with a table reference. Referenced rows must exist, and rows still in use cannot be removed. Nothing was changed.
```

Deleting Northwind Cafe while an order still points at it gets the same answer. Dropping `customers` is refused with “Remove references to this table first. Nothing was changed.”, and dropping or retyping a linked field with “Remove the reference on this field first. Nothing was changed.”

A connection that can't read every row of the linked table can't set or change a link at all. It gets `FORBIDDEN`, “Changing reference keys requires read access to all rows of the related field.”, whether the row exists or not, so a link can't be used to learn which rows exist.

## Join the tables with SQL

“What has each customer ordered, and for how much?” is one `query`:

```sql
SELECT c.name AS customer, o.ordered_on, sum(i.quantity * i.unit_price) AS total
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items i ON i.order_id = o.id
GROUP BY c.name, o.ordered_on
ORDER BY o.ordered_on
```

| customer | ordered_on | total |
| --- | --- | --- |
| Northwind Cafe | 2026-10-27 | 161.40 |

Join on equal fields, as here. A join with no such condition between two tables, such as a cross join, is refused. The [API reference](/docs/reference/api/) lists the SQL a query may use.

## How an assistant sees the links

`list_references` returns each link with both ends, for example `orders.customer_id` to `customers.id`, and `onDelete` `restrict`. The `adoor_columns` view marks each table's `id` as its primary key. An assistant reads both before it writes a join, so you can ask “Which customers have open orders?” in plain words.

A link is listed only when the connection can see both fields. A field hidden from an assistant hides its links too.

## Undo and links

Undo follows the same rule as any other write: it never leaves a link pointing at nothing.

- Undo adding an order while its items still point at it, and undo is refused with `UNDO_CONFLICT`: “This restoration conflicts with a table reference. Nothing was changed.” Undo the items first, then the order.
- Undo deleting an order and its items in the reverse order: the order first, then the items. The order comes back with the same `id`, so its items link to it again.
- Undo stops if someone edited the same rows later.

## Remove a link

`remove_reference` takes the link's `id` from `list_references`. It drops the foreign key and keeps the field and every value in it. It needs drop_reference in both spaces, and it is recorded in the history like adding one.