Table definitions

Wiki / Tables and SQL

On this page

• Constraints
• Indexes
• ALTER TABLE
• Table types
• Global indexes
• Reading every table of a type
• Privileges

From version 2.1.0.

Constraints

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) can be indexed, several to an index, used from the leading column on.

ALTER TABLE

// 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;

• 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:

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;

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

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

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