Foreign keys

How KDBS checks that a child row's reference names a parent row that exists, and what that check costs.

A foreign key in KDBS makes the engine refuse any write that would leave a child row pointing at a parent row that does not exist. You declare it with REFERENCES <parent> on a column in CREATE TABLE. The child column always holds the parent’s primary key, and the only action is RESTRICT: a parent row that still has children cannot be deleted. The check runs inside the writing statement, so a script, a service and an agent all get the same rule without writing any join to confirm it.

The idea

Every KDBS row has an immutable primary key, called the Keystone id (see Row identity and primary keys). A foreign key stores that id. Because a primary key can never be updated, there is no ON UPDATE case to handle. Because a deleted parent’s key stays bound until PURGE frees it, and RESTRICT refuses that delete while a child still names the parent, a child’s reference cannot come to name a different parent row.

CREATE TABLE lounge_visits (id int64, passenger_id int64 REFERENCES passengers, minutes int64) BTREE;

You write no parent column, and you may not write one: the parent side is always the parent’s primary key.

How it works

There are two checks. Both are reads that run inside the writing statement’s own path. There is no trigger system and nothing is written to the other table.

child INSERT / UPDATE of the fk column        parent DELETE
            |                                        |
   forward check (before the row is written)   reverse check (per deleted row)
            |                                        |
   take IS on parent table, S on parent row     Cabin on child fk column?
            |                                     | yes, value observed -> answer from the Cabin
   descend the parent's primary-key tree          | no                  -> walk the child table
            |                                        |
   parent visible  -> pass                      visible child   -> FK_VIOLATION
   parent missing  -> FK_VIOLATION              undecided child -> wait, then check again
   parent being written -> wait, then rerun     no child        -> delete proceeds

Forward check: INSERT and UPDATE of the child

Before any row is written, the statement collects every distinct parent key it names. A 1,000-row insert that names one parent costs one check.
For each distinct parent it takes an intention lock (IS) on the parent table and a shared lock (S) on the parent row, and only then reads the row. Both locks are held until the transaction commits or rolls back.
A visible, committed parent passes. A missing or deleted parent fails with FK_VIOLATION. A parent that another transaction is writing makes the statement wait for that transaction and then run again.
A NULL in a nullable fk column skips the check (MATCH SIMPLE). An integer that cannot be a primary key (below 1, or above the id range) fails without a lookup.

Because the child writer holds S on the parent row until it ends, a concurrent DELETE of that parent waits for it. This closes the window between a passing check and the child’s commit.

Reverse check: DELETE of the parent

For each parent row being deleted, the engine asks whether any visible child still references it. It walks the child table and stops at the first visible match, which fails with FK_VIOLATION. A child row that an undecided transaction is writing makes the DELETE wait for that writer and check again.

A full walk of the child table per deleted parent is expensive. If the child’s fk column has a Cabin, the reverse check reads it first. For an observed value, the Cabin’s set is authoritative: a live child in it fails the delete, and an exhausted set answers “no children” without walking the table. Nothing creates that Cabin for you; declare it with CABIN on the column or with CREATE CABIN. The reverse check reads the Cabin but never adds values to it, because a parent key is deleted only once.

There is no reverse check for a parent UPDATE: the referenced column is the primary key, which cannot be updated.

Example

Run against the sample passengers table. The sample tickets table already references passengers 1 to 8, so the example first adds two passengers of its own:

kdbs> INSERT INTO passengers VALUES ('Seo Dahye', 1, '2022-04-05')
INSERTED oid=4005 id=9 page=133 slot=8
kdbs> INSERT INTO passengers VALUES ('Moon Jaewon', 0, '2026-03-01')
INSERTED oid=4005 id=10 page=133 slot=9
kdbs> CREATE TABLE lounge_visits (id int64, passenger_id int64 REFERENCES passengers, minutes int64) BTREE
CREATED oid=4027
kdbs> SHOW FKEYS
fkeys=4
fk_id=1 child=flights column=airline_id parent=airlines action=RESTRICT nullable=no
fk_id=2 child=tickets column=flight_id parent=flights action=RESTRICT nullable=no
fk_id=3 child=tickets column=passenger_id parent=passengers action=RESTRICT nullable=no
fk_id=4 child=lounge_visits column=passenger_id parent=passengers action=RESTRICT nullable=no
kdbs> INSERT INTO lounge_visits VALUES (9, 45)
INSERTED oid=4027 id=1 page=145 slot=0
kdbs> INSERT INTO lounge_visits VALUES (99, 45)
                                        ^
ERR FK_VIOLATION retryable=0 'passenger_id' references row id=99 of 'passengers', which does not exist (byte 34)
kdbs> DELETE FROM passengers WHERE id = 9
ERR FK_VIOLATION retryable=0 row id=9 is still referenced by 'lounge_visits.passenger_id'
kdbs> DELETE FROM passengers WHERE id = 10
DELETED 1

The first three foreign keys come with the sample tables. A DELETE of passenger 1 is refused the same way, naming tickets.passenger_id:

kdbs> DELETE FROM passengers WHERE id = 1
ERR FK_VIOLATION retryable=0 row id=1 is still referenced by 'tickets.passenger_id'

With a Cabin on the child column, an observed value answers the reverse check without a walk. Here CABIN creates the Cabin at CREATE TABLE, and the SELECT observes the value 10, which no visit has. The example starts again from a fresh sandbox, with the same two passengers added:

kdbs> INSERT INTO passengers VALUES ('Seo Dahye', 1, '2022-04-05')
INSERTED oid=4005 id=9 page=133 slot=8
kdbs> INSERT INTO passengers VALUES ('Moon Jaewon', 0, '2026-03-01')
INSERTED oid=4005 id=10 page=133 slot=9
kdbs> CREATE TABLE lounge_visits (id int64, passenger_id int64 REFERENCES passengers CABIN, minutes int64) BTREE
CREATED oid=4027
kdbs> INSERT INTO lounge_visits VALUES (9, 45)
INSERTED oid=4027 id=1 page=145 slot=0
kdbs> SELECT * FROM lounge_visits WHERE passenger_id = 10
kdbs> DELETE FROM passengers WHERE id = 10
DELETED 1
kdbs> SHOW ACCESS
access_shapes=5
kind=Lookup rel=airlines columns=[id] uses=3 last_seen=1227699027588481
kind=Lookup rel=flights columns=[id] uses=3 last_seen=1227699034127991
kind=Lookup rel=passengers columns=[id] uses=9 last_seen=1227699046558817
kind=CabinProbe rel=lounge_visits columns=[passenger_id] uses=2 last_seen=1227699048371713
kind=FilterScan rel=tickets columns=[passenger_id] uses=1 last_seen=1227699048368228

The SELECT returned no rows. The Lookup lines are the forward checks, including those the sample rows made when they were loaded. The DELETE checked both children: lounge_visits through its Cabin, and tickets, which has no Cabin on passenger_id, with a FilterScan. Without the Cabin, lounge_visits is recorded as a FilterScan too.

What it guarantees

A foreign key is a constraint, the top rung of the trust ladder: it is authoritative, and it can fail a write. It is not advisory. The checks read the latest committed state at the moment of the check, not the statement’s snapshot, so a parent deleted and committed after your snapshot was taken still fails the check. A check that meets an undecided writer waits instead of guessing.

What it costs

Forward check: one primary-key descent per distinct parent per statement, counted in the statement’s max_rows_touched budget like any other read.
Reverse check: a full walk of the child table per deleted parent row, unless a Cabin on the child column answers. A violation stops at the first child found; a pass costs the whole table.
Held parent locks. The S lock a child writer holds on its parent row has side effects:
- Any UPDATE of the parent row waits while a transaction that wrote a child of it is open, not only a DELETE.
- Two transactions that each write a child of parent P and then update P deadlock. The second to ask is refused, naming the deadlock.
- Each distinct parent’s S counts against max_locks_per_txn. A bulk insert in which every row names a different parent reaches the cap at about half the rows it otherwise would, and is then refused ResourceExhausted.
- CREATE INDEX, DROP INDEX and CREATE ASSERTION on a parent table wait for every open child writer.
A check that fails gives back the parent row’s S lock, so an FK_VIOLATION does not block later inserts of that parent key.

How to observe it

SHOW FKEYS lists every declaration, with action=RESTRICT and nullable=.
DESCRIBE <child> shows references=<parent> on the declaring column.
SHOW ACCESS records the checks next to ordinary query shapes: a Lookup on the parent’s primary key for the forward check, and a FilterScan, or a CabinProbe when a Cabin answered, on the child’s fk column for the reverse check. This lets you compare constraint cost against query cost.
ANALYZE wraps only SELECT, so it shows nothing for the checks.

Configuration

There is no switch for foreign keys. These settings affect them:

Key	Default	Effect
`max_locks_per_txn`	`65536`	Each distinct parent’s `S` lock counts against it.
`lock_wait_fault_net_ms`	`1000`	How long a wait may run before it is treated as a fault and the waiter is refused with the retryable `TXN_CONFLICT`.
`cabin_max_values`, `cabin_max_entries_per_value`	`4096`, `4096`	Caps on a Cabin that serves the reverse check.

See Server configuration.

Limitations

RESTRICT only. ON DELETE CASCADE and SET NULL do not exist; the grammar stops at REFERENCES <parent>.
Declared only in CREATE TABLE. There is no ADD CONSTRAINT, and nothing checks existing rows.
One column, referencing the parent’s primary key. Composite foreign keys and REFERENCES parent(col) are refused.
Immediate checks only. DEFERRABLE is not supported.
The parent must be a BTREE table. Every new table is one; an older heap table is refused as a parent.
No self-reference. A table cannot reference itself.
No computed fk values in UPDATE. A SET value for an fk column that reads a column or NOW() is refused NOT_IMPLEMENTED. A constant or a scalar subquery is allowed.
Known gap: a statement that fails for a reason other than a foreign-key violation, such as a duplicate key or an assertion, keeps the S locks on the absent parents it resolved until the transaction ends. See Known gaps.