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:

{
  "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:

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:

{ "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:

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:

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:

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:

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

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 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.

This page as Markdown