# Tables and types

> CREATE TABLE, the column types, and what a unique key buys you.

## CREATE TABLE

```sql
CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  employee_id INTEGER,
  status VARCHAR(20) NOT NULL,
  total DOUBLE PRECISION NOT NULL,
  refunded BOOLEAN NOT NULL,
  placed_at TIMESTAMP NOT NULL
)
```

Columns are nullable unless marked `NOT NULL`. Exactly one column may be `PRIMARY KEY` — written
on the column or as a table-level `PRIMARY KEY (order_id)` — and it is the table's unique key: the
column that `UPDATE`, `DELETE`, and `ON CONFLICT` address rows through, and the only uniqueness
the engine enforces.

`CREATE TABLE IF NOT EXISTS` leaves an existing table of that name alone, and `CREATE TABLE …
AS SELECT` takes both its columns and its first rows from a query:

```sql
CREATE TABLE completed_orders AS
SELECT order_id, customer_id, total FROM orders WHERE status = 'completed'
```

A `CHECK` constraint is a row condition over the table's own columns, and it runs on every path
that writes a row — insert, upsert, and update, which is checked against the row as it will be
once the update lands:

```sql
CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  total DOUBLE PRECISION NOT NULL CHECK (total >= 0),
  status VARCHAR(20) NOT NULL,
  CONSTRAINT settled_orders_have_a_total CHECK (status <> 'completed' OR total > 0)
)
```

A constraint fails only when it evaluates to false, so SQL's unknown passes: a NULL column
satisfies `CHECK (total >= 0)` unless the column is also `NOT NULL`.

A `FOREIGN KEY` references another table's unique key — the column the engine can probe for
existence, and the one its keyed writes address rows by:

```sql
CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
  note_id INTEGER REFERENCES notes(note_id) ON DELETE SET NULL
)
```

Every write of a referencing column checks that the parent row exists, reading through the writing
transaction, so a child inserted beside its parent in one scope sees it. A NULL reference names no
parent and is satisfied.

`ON DELETE` takes `RESTRICT` (the default), `CASCADE`, and `SET NULL`, and the action runs inside
the deleting transaction — a parent and its dependents publish together or not at all. `ON UPDATE`
has nothing to act on, because a unique key cannot change.

A table without a `PRIMARY KEY` is append-only. That is a reasonable choice for an event log, and
a mistake for anything a user edits, because it cannot be changed later without recreating the
table.

## Column types

Four logical types, chosen because they are what a browser can store and compare without
ambiguity. The usual SQL spellings map onto them:

| Type       | SQL spellings                                                                     | JavaScript |
| ---------- | --------------------------------------------------------------------------------- | ---------- |
| `number`   | `INTEGER`, `BIGINT`, `SMALLINT`, `DOUBLE PRECISION`, `REAL`, `NUMERIC`, `DECIMAL` | `number`   |
| `string`   | `VARCHAR(n)`, `TEXT`, `CHAR(n)`                                                   | `string`   |
| `boolean`  | `BOOLEAN`                                                                         | `boolean`  |
| `datetime` | `TIMESTAMP`, `DATE`                                                               | `Date`     |

Widths in `VARCHAR(80)` are accepted and ignored — they document intent, and nothing truncates.
`NUMERIC` is IEEE-754 double precision, not arbitrary precision: money is safe to the cent in the
ranges an application deals with, but this is not the engine to settle accounts in.

Types the engine deliberately does not have — `JSON`/`JSONB`, arrays, `UUID`, `INTERVAL` as a
stored type, enums as a database type — are rejected at `CREATE TABLE` rather than silently
stored as text.

## Defaults

A column can declare a default in SQL, either a constant or `CURRENT_TIMESTAMP`:

```sql
CREATE TABLE events (
  event_id INTEGER PRIMARY KEY,
  kind TEXT NOT NULL,
  source TEXT DEFAULT 'app',
  noted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
```

A column with a default is `NOT NULL` unless declared otherwise, because the default is what an
absent value means — NULL and the default cannot both claim the slot.

The same defaults, and the `autoincrement` kind that SQL has no spelling for, are available
through `createTable`, which is also the form the [schema DSL](/docs/schema.md) compiles to:

```ts
await db.createTable({
  name: "events",
  uniqueKey: "event_id",
  columns: [
    { name: "event_id", type: "number", defaultValue: { kind: "autoincrement" } },
    { name: "kind", type: "string" },
    { name: "noted_at", type: "datetime", defaultValue: { kind: "now" } },
    { name: "source", type: "string", defaultValue: { kind: "literal", value: "app" } },
  ],
});
```

Three kinds: `now`, a `literal`, and `autoincrement` on the key column. They fill null-or-absent
slots at insert time and are never applied at read time, so adding a default later does not
rewrite the rows already stored. Arbitrary expressions are deliberately not representable — the
spec is stored in the catalog and crosses the worker boundary, so it has to be plain data.

## Evolving a table

```sql
ALTER TABLE orders ADD COLUMN channel TEXT
```

Tables can gain nullable columns and widen a `NOT NULL` column to nullable. Both are catalog-only
changes: no blocks are rewritten, so they are effectively instant however large the table is. A
column added to a table with rows in it is always nullable — the rows already stored have no value
for it — though a `DEFAULT` fills the rows written afterwards.

`DROP TABLE` takes the table's rows, its catalog record, its full-text index, and its triggers.
The blocks are retired through the commit rather than deleted, so a reader pinned to an older
version keeps resolving the bytes it already holds and the collector reclaims them once nobody
can reach them:

```sql
DROP TABLE IF EXISTS old_orders
```

What a pinned reader does lose is the table itself — the catalog has one present tense, so a
snapshot open across a drop sees the table disappear rather than a frozen copy of it. A table
another table's trigger writes to cannot be dropped; the trigger would fail at every firing.

Narrowing a column, changing its type, dropping it, or adding a `NOT NULL` column to a table with
rows in it are all refused — each would need a rewrite of every block, and doing that silently
behind a DDL statement is how a browser tab freezes.

The [schema DSL](/docs/schema.md) plans those changes for you from a declared schema, with
`migrate()` applying only the steps that are safe.

## Reading the catalog

```ts
const tables = await db.listTables();
// [{ name: "orders", columns: [{ name: "order_id", type: "number", nullable: false }, …] }]
```

This is what the devtools schema rail and the SQL editor's autocompletion are built on.

---

Minnow 0.1.0 · this page on the site: /docs/sql/ddl/