# Table definitions

[Wiki](../README.md) / [Tables and SQL](README.md)

**On this page**

- [Constraints](#constraints)
- [Indexes](#indexes)
- [ALTER TABLE](#alter-table)
- [Table types](#table-types)
- [Global indexes](#global-indexes)
- [Reading every table of a type](#reading-every-table-of-a-type)
- [Privileges](#privileges)

From version 2.1.0.

## Constraints

| Constraint | Written |
|---|---|
| Primary key | `id INTEGER PRIMARY KEY`, or `PRIMARY KEY (a, b)`; its columns are `NOT NULL` |
| Not null, default | `qty INTEGER NOT NULL DEFAULT 1` |
| Unique | `email STRING UNIQUE`, `UNIQUE [name] (a, b)`, `CONSTRAINT name UNIQUE (a, b)` |
| Check | `qty INTEGER CHECK (qty > 0)`, `CHECK (…)`, `CONSTRAINT name CHECK (…)` |

A `CHECK` is a SQL expression over the row's own columns, without
parameters, subqueries or aggregates, checked on every insert and update. It
fails only when false: null passes, as in SQL. Unnamed checks are named
`check_1`, `check_2`, … by position; unnamed unique indexes `unique_<cols>`.
Nulls never collide in a unique index.

## Indexes

`INDEX [name] (cols)` in the definition, or `ALTER TABLE … ADD INDEX`. An
index is local to its table, so "this customer's orders by date" is one short
range. Columns of the indexable types ([Column types](README.md#column-types))
can be indexed, several to an index, used from the leading column on.

## ALTER TABLE

```cypher
// Every customer's table, in one statement
MATCH (c:Customer)
ALTER TABLE c.people ADD COLUMN first_name STRING, ADD COLUMN last_name STRING
WITH c CALL (c) SQL { UPDATE c.people SET first_name = split_part(full_name, ' ', 1),
                                          last_name = split_part(full_name, ' ', 2) }
WITH c ALTER TABLE c.people DROP COLUMN full_name;
```

| Change | Rows |
|---|---|
| `ADD [COLUMN] [IF NOT EXISTS] name TYPE …` (with `NOT NULL`, `DEFAULT`, `UNIQUE`, `CHECK`) | Kept; earlier rows read the default |
| `DROP [COLUMN] [IF EXISTS] name` | Rewritten without it |
| `RENAME [COLUMN] a TO b` | Rewritten under the new name; checks follow the rename |
| `ALTER [COLUMN] x [SET DATA] TYPE t [USING (sql)]` | Converted; `USING` computes the new value from the row |
| `ALTER [COLUMN] x SET DEFAULT e`, `DROP DEFAULT` | Rewritten, so earlier rows keep the default they read |
| `ALTER [COLUMN] x SET NOT NULL`, `DROP NOT NULL` | Checked |
| `ADD [UNIQUE] INDEX`, `ADD [CONSTRAINT n] UNIQUE (…)`, `ADD [CONSTRAINT n] CHECK (…)` | Checked |
| `DROP INDEX [IF EXISTS] n`, `DROP CONSTRAINT [IF EXISTS] n` | Kept |
| `SET TYPE Name` | Joins a [table type](#table-types) |

- One statement may make several changes, joined by commas. However many, it
  stores one new schema, shared by every table altered alike, and rewrites the
  rows at most once.
- The statement is all or nothing: a change that fails (a value that does not
  convert, rows that break a new constraint) undoes the others.
- A column in the primary key, an index or a check is dropped after them; the
  primary key itself is fixed when the table is created.
- A node without the table is skipped.

## Table types

A table type is a named definition, created once and used on any node:

```cypher
CREATE TABLE TYPE Order (
  id        INTEGER PRIMARY KEY,
  placed_at LOCAL DATETIME NOT NULL,
  sku       STRING NOT NULL,
  amount    DECIMAL(12, 2) NOT NULL,
  INDEX by_date (placed_at),
  GLOBAL INDEX orders_by_sku (sku, placed_at)
);

MATCH (c:Customer) WHERE c.orders IS NULL
SET c.orders = TABLE Order;

ALTER TABLE TYPE Order ADD COLUMN currency STRING NOT NULL DEFAULT 'GBP';

CREATE CONSTRAINT customer_orders FOR (c:Customer) REQUIRE c.orders IS :: TABLE Order;
```

| Statement | Does |
|---|---|
| `CREATE TABLE TYPE [IF NOT EXISTS] Name (…)` | Defines the type |
| `SET n.t = TABLE Name` | Gives the node an empty table of the type |
| `ALTER TABLE TYPE Name …` | The changes of `ALTER TABLE`, for every table of the type at once |
| `ALTER TABLE n.t SET TYPE Name` | An existing table joins the type; each of its columns must be the type's, and columns it lacks read their defaults |
| `SHOW TABLE TYPES`, `CALL sql.types()` | Name, columns, indexes, tables and rows of each type, and its definition |
| `DROP TABLE TYPE [IF EXISTS] Name` | Drops a type no table uses |
| `DROP TABLE TYPE Name DETACH` | Every table of the type keeps its rows and columns as a table of its own; then the type goes |
| `REQUIRE n.t IS :: TABLE Name` | Every such property is a table of the type (`IS :: TABLE`: any table) |

Tables of a type change only with it: `ALTER TABLE` on one is refused, naming
the type. A type that a constraint names is dropped after the constraint.
Type statements stand alone in a statement and run outside explicit
transactions, like index and constraint commands.

## Global indexes

`GLOBAL INDEX [name] (cols)` in a type indexes the rows of every table of the
type together: one range of `sku` values spans every customer. It is kept in
the same transaction as each row change, table copy, type change and drop. A
global index is not unique; a unique index holds within each table.

## Reading every table of a type

```cypher
CALL SQL {
  SELECT owner(o) AS c, o.id AS order_id, o.amount AS amount
  FROM Order AS o
  WHERE o.sku = $sku AND o.placed_at >= $since
  ORDER BY o.placed_at DESC LIMIT 50
}
MATCH (c)-[:LIVES_IN]->(city:City)
RETURN city.name, c.name, order_id, amount;
```

`FROM Order AS o` reads the rows of every table of type `Order`, and
`owner(o)` gives the node holding each row's table. The `WHERE` must bound
the leading columns of a global index; a read of every table is written
`FROM Order ALLOW SCAN`, so it is always a choice. A table counts when the
property it was created for holds it in the reading transaction.

## Privileges

| Statement | Needs |
|---|---|
| `SELECT`, `SQL { }`, `sql.query`, `sql.page`, `sql.explain` | `READ` (`MATCH`) |
| `INSERT`, `UPDATE`, `DELETE`, `SET n.t = TABLE …`, `ALTER TABLE` | `WRITE` |
| `CREATE`, `ALTER`, `DROP TABLE TYPE` | `TABLE TYPE MANAGEMENT`, held by `architect` and `admin` (`GRANT TABLE TYPE MANAGEMENT ON DATABASE x TO role`) |
| `SHOW TABLE TYPES`, `sql.types` | `SHOW CONSTRAINT` |
| `CALL gdb.compact()` | Administrative execution, as `gdb.checkpoint` |

A security catalog from 2.0 gives `architect` and `admin` the new privilege
as it loads.