Relationships & Foreign Keys

Overview

SyndrDB provides a complete Relationships/Foreign Keys system for defining referential integrity between bundles. Relationships enable foreign key validation on INSERT and UPDATE operations, CASCADE DELETE support, automatic field and index creation, and GraphQL relationship resolution.

  • Foreign Key Validation — Ensures referenced parent documents exist before inserts and updates
  • Cascade Delete — Recursively removes dependent documents when a parent is deleted
  • Automatic Setup — Creates FK fields, hash indexes, and GraphQL schema entries automatically

Table of Contents

Creating Relationships

Syntax

UPDATE BUNDLE "SourceBundleName" ADD RELATIONSHIP (
  "RelationshipName" {
    "RelationshipType",
    "SourceBundle",
    "SourceField",
    "DestinationBundle",
    "DestinationField"
  }
);

Components

Component Description Required
RelationshipName User-provided identifier (e.g., "Authors_Books_1") Yes
RelationshipType One of 1toMany, 0toMany, ManyToMany Yes
SourceBundle Parent bundle name Yes
SourceField Field in source bundle (defaults to "DocumentID" if empty) Yes
DestinationBundle Child bundle containing the foreign key Yes
DestinationField FK field name in destination (defaults to SourceBundleName + "ID" if empty) Yes

Examples

-- One-to-Many: Authors to Books
UPDATE BUNDLE "Authors" ADD RELATIONSHIP (
  "Authors_Books_1" {
    "1toMany",
    "Authors",
    "author_id",
    "Books",
    "author_fk"
  }
);

-- Many-to-Many: Students to Courses
UPDATE BUNDLE "Students" ADD RELATIONSHIP (
  "Students_Courses_1" {
    "ManyToMany",
    "Students",
    "student_id",
    "Courses",
    "course_id"
  }
);

Relationship Types

Type Description FK Required Auto-Created Fields
1toMany One document in source can have many in destination Yes Adds FK field to destination with required=true
0toMany Source document can have zero or more related documents No (optional) Adds FK field to destination with required=false
ManyToMany Bidirectional relationship between bundles Both optional Adds FK field to both bundles (SourceBundleName+"ID" on destination, DestinationBundleName+"ID" on source)

Dropping Relationships

Syntax

UPDATE BUNDLE "BundleName" DROP RELATIONSHIP "RelationshipName";

Behavior

Dropping a relationship removes the relationship metadata only. It preserves all of the following:

  • Foreign key fields and their data
  • Auto-created hash indexes
  • Existing documents and their FK values

The GraphQL schema is updated to remove the relationship fields from both bundles.

Automatic Actions

When a relationship is created, SyndrDB automatically performs the following actions:

1. Field Creation

A foreign key field is added to the destination bundle. The field type matches the source field type (INT, STRING, etc.). The required status is determined by the relationship type:

  • 1toMany — FK field is required (required=true)
  • 0toMany — FK field is optional (required=false)
  • ManyToMany — FK fields are added to both bundles (both optional)

2. Index Creation

A hash index is automatically created on the source field, named {SourceFieldName}_fk. This index enables O(1) lookups during foreign key validation on INSERT and UPDATE operations.

3. GraphQL Schema

The GraphQL schema is regenerated to include relationship fields on both bundles, enabling forward and reverse relationship traversal through the GraphQL API.

Foreign Key Validation

On INSERT

When inserting a document into a bundle that has a foreign key relationship, SyndrDB validates that the referenced parent document exists. The hash index on the source field is used for O(1) lookup performance. If the parent document does not exist, the insert fails with an error.

On UPDATE

When updating a document's foreign key field, SyndrDB validates that the new FK value references an existing parent document. An operation-scoped cache is used for batch updates to avoid redundant lookups.

Error Example

Foreign key violation: field 'author_fk' = '999'
does not exist in bundle 'Authors'.DocumentID |
Suggested: Ensure value '999' exists in Authors.DocumentID before updating

Cascade Delete

When deleting a parent document that has dependent children, SyndrDB supports two behaviors:

Behavior Description
RESTRICT (default) Blocks deletion if dependent documents exist in child bundles
CASCADE Recursively deletes all dependent documents (up to 10 levels deep with circular reference detection)

Cascade Process

The cascade delete process follows these steps:

  1. ValidateDeleteWithCascade() analyzes what would be deleted across all related bundles
  2. A CascadeDeletePlan is generated showing affected bundles and document counts
  3. The user confirms the cascade operation
  4. ExecuteCascadeDelete() runs within a transaction for atomicity
  5. Documents are deleted deepest-level first to maintain referential integrity throughout the process

Important: Cascade depth is limited to 10 levels. Circular references are detected and prevented from causing infinite recursion.

GraphQL Integration

Relationships automatically appear in GraphQL schemas. The cardinality of the relationship determines whether the field resolves to an array or a single object.

Example Schema

type Author {
  DocumentID: String!
  author_id: Int!
  name: String
  books: [Book!]!  # 1toMany relationship
}

type Book {
  DocumentID: String!
  title: String
  author_fk: Int!
  author: Author!  # Reverse relationship
}

Both forward and reverse relationships are supported:

  • Forward — Parent to children (e.g., Author.books returns all related books)
  • Reverse — Child to parent (e.g., Book.author returns the parent author)

Cardinality mapping:

Relationship Type Forward Field Reverse Field
1toMany [ChildType!]! (array) ParentType! (single object)
0toMany [ChildType!] (nullable array) ParentType (nullable single object)
ManyToMany [RelatedType!] (array) [RelatedType!] (array)

Best Practices

Do

  • Use descriptive relationship names that indicate the bundles and direction (e.g., "Authors_Books_1")
  • Always define the source field explicitly rather than relying on defaults
  • Use 0toMany for optional associations where child documents may not always reference a parent
  • Test FK validation before inserting bulk data to catch schema mismatches early
  • Plan your cascade delete strategy before creating relationships in production

Don't

  • Don't create circular relationships (e.g., A references B, B references A) — this can cause issues with cascade delete
  • Don't drop relationships without verifying that dependent data and application logic have been updated
  • Don't use ManyToMany when 1toMany suffices — ManyToMany adds fields to both bundles and has higher overhead
  • Don't assume dropping a relationship removes the auto-created fields or indexes — they are preserved

Limitations

Limitation Details
No CASCADE UPDATE Only cascade delete is implemented. Updating a parent's primary key does not propagate to child FK fields.
No 1to1 relationship type One-to-one relationships are not yet supported as a distinct type. Use 1toMany with application-level enforcement.
Cascade depth limit Cascade delete is limited to 10 levels of depth. Deeply nested relationship chains beyond this limit will be rejected.
Drop preserves artifacts Dropping a relationship does not remove auto-created FK fields or hash indexes. These must be removed manually if needed.
FK type matching The foreign key field type must match the source field type. Type mismatches will cause validation errors.

Quick Reference

Command Description
UPDATE BUNDLE "b" ADD RELATIONSHIP (...) Create a new relationship with FK field, index, and GraphQL schema
UPDATE BUNDLE "b" DROP RELATIONSHIP "name" Remove relationship metadata (preserves fields and indexes)
1toMany One parent to many children, FK required
0toMany Zero or more children, FK optional
ManyToMany Bidirectional, FK fields on both bundles

Last updated: March 2026