Skip to main content

Relationships and Joins

CSDB represents relationships with foreign keys. Joins are runtime operations performed by readers or libraries.

CSDB v1 does not store persisted join results.

Foreign Keys​

Foreign keys are declared in table schemas.

foreign_keys:
  - name: sessions_worker_fk
    columns: [worker_id]
    references:
      table: workers
      columns: [id]
    on_delete: restrict
    on_update: cascade
    relationship: worker

columns are columns in the current table.

references.table is the target table.

references.columns are target columns in the target table.

relationship is a stable name a library may use for object navigation.

Referential Actions​

Allowed values:

restrict
cascade
set_null
set_default
no_action

Referential actions describe intended behavior for tools that modify CSDB data. They do not require a database server.

Join Behavior​

A runtime join uses:

  • A foreign key declaration.
  • The target table primary key or unique index.
  • The target table row index when available.

Example relationship:

sessions.worker_id -> workers.id

Equivalent SQL-style join:

SELECT *
FROM sessions
JOIN workers ON sessions.worker_id = workers.id;

For efficient joins, foreign key columns should have schema indexes and target key columns should be represented in row_index.