@bernierllc/validators-referential-integrity

Primitive validator for referential integrity - foreign key relationships and cross-document validation.

Installation

npm install @bernierllc/validators-referential-integrity

Overview

This package provides comprehensive referential integrity validation for data structures with foreign key relationships. It validates:

• Foreign Key Existence: Ensures all foreign keys reference existing records
• Orphaned Record Detection: Detects records that should have been cascade deleted
• Circular Reference Detection: Identifies cycles in foreign key relationships
• Cascade Delete Validation: Validates cascade delete constraints (RESTRICT, CASCADE, SET_NULL)

Usage

Basic Validation

import {
  validateReferentialIntegrity,
  type ReferentialIntegrityData,
} from '@bernierllc/validators-referential-integrity';

const data: ReferentialIntegrityData = {
  schemas: [
    {
      collection: 'users',
      primaryKey: 'id',
      foreignKeys: [],
    },
    {
      collection: 'posts',
      primaryKey: 'id',
      foreignKeys: [
        {
          sourceField: 'userId',
          targetCollection: 'users',
          targetField: 'id',
          required: true,
          onDelete: 'CASCADE',
        },
      ],
    },
  ],
  documents: {
    users: [
      { id: 1, name: 'Alice' },
      { id: 2, name: 'Bob' },
    ],
    posts: [
      { id: 1, userId: 1, title: 'Hello World' },
      { id: 2, userId: 999, title: 'Invalid Post' }, // Foreign key violation
    ],
  },
};

const problems = await validateReferentialIntegrity(data);
console.log(problems);
// [
//   {
//     ruleId: 'referential-integrity/foreign-key-existence',
//     message: "Foreign key 'userId' references non-existent record '999' in 'users'",
//     severity: 'error',
//     domain: 'schema',
//     ...
//   }
// ]

Check Validity

import { isReferentialIntegrityValid } from '@bernierllc/validators-referential-integrity';

const isValid = await isReferentialIntegrityValid(data);
if (isValid) {
  console.log('All foreign keys are valid!');
}

Safe Validation with Results

import { validateReferentialIntegritySafe } from '@bernierllc/validators-referential-integrity';

const result = await validateReferentialIntegritySafe(data);
console.log(result);
// {
//   success: false,
//   problems: [...],
//   errorCount: 1,
//   warningCount: 0
// }

Configuration Options

interface ReferentialIntegrityOptions {
  /**
   * Whether to check for foreign key existence
   * @default true
   */
  checkForeignKeys?: boolean;

  /**
   * Whether to check for orphaned records
   * @default true
   */
  checkOrphanedRecords?: boolean;

  /**
   * Whether to detect circular references
   * @default true
   */
  checkCircularReferences?: boolean;

  /**
   * Whether to validate cascade delete constraints
   * @default true
   */
  checkCascadeDelete?: boolean;

  /**
   * Maximum depth for circular reference detection
   * @default 100
   */
  maxDepth?: number;

  /**
   * Whether to allow null foreign keys
   * @default true
   */
  allowNullForeignKeys?: boolean;
}

Using Options

const problems = await validateReferentialIntegrity(data, {
  checkCircularReferences: false, // Skip circular reference checks
  maxDepth: 50, // Lower depth limit
  allowNullForeignKeys: false, // Require all FKs to be non-null
});

Schema Definition

Foreign Key Relationship

interface ForeignKeyRelationship {
  sourceField: string; // Field containing the foreign key
  targetCollection: string; // Collection being referenced
  targetField: string; // Field being referenced (usually 'id')
  required?: boolean; // Whether FK is required (not nullable)
  onDelete?: 'CASCADE' | 'SET_NULL' | 'RESTRICT' | 'NO_ACTION';
}

Schema Structure

interface ReferentialIntegritySchema {
  collection: string; // Name of the collection
  primaryKey?: string; // Primary key field (default: 'id')
  foreignKeys?: ForeignKeyRelationship[]; // Foreign key definitions
}

Examples

Multi-Collection Validation

const data = {
  schemas: [
    {
      collection: 'users',
      foreignKeys: [],
    },
    {
      collection: 'categories',
      foreignKeys: [],
    },
    {
      collection: 'posts',
      foreignKeys: [
        {
          sourceField: 'userId',
          targetCollection: 'users',
          targetField: 'id',
          required: true,
        },
        {
          sourceField: 'categoryId',
          targetCollection: 'categories',
          targetField: 'id',
        },
      ],
    },
  ],
  documents: {
    users: [{ id: 1, name: 'Alice' }],
    categories: [{ id: 1, name: 'Tech' }],
    posts: [
      { id: 1, userId: 1, categoryId: 1, title: 'Valid Post' },
      { id: 2, userId: 999, categoryId: 1, title: 'Invalid User' },
      { id: 3, userId: 1, categoryId: 999, title: 'Invalid Category' },
    ],
  },
};

const problems = await validateReferentialIntegrity(data);
// Returns 2 problems: missing userId 999 and missing categoryId 999

Circular Reference Detection

const data = {
  schemas: [
    {
      collection: 'nodes',
      foreignKeys: [
        {
          sourceField: 'parentId',
          targetCollection: 'nodes',
          targetField: 'id',
        },
      ],
    },
  ],
  documents: {
    nodes: [
      { id: 1, parentId: 2 },
      { id: 2, parentId: 3 },
      { id: 3, parentId: 1 }, // Creates a cycle: 1 -> 2 -> 3 -> 1
    ],
  },
};

const problems = await validateReferentialIntegrity(data);
// Detects the circular reference

Cascade Delete Validation

const data = {
  schemas: [
    {
      collection: 'users',
      foreignKeys: [],
    },
    {
      collection: 'posts',
      foreignKeys: [
        {
          sourceField: 'userId',
          targetCollection: 'users',
          targetField: 'id',
          onDelete: 'RESTRICT', // Cannot delete user if posts exist
        },
      ],
    },
  ],
  documents: {
    users: [], // User was deleted
    posts: [{ id: 1, userId: 1, title: 'Post' }], // Should have prevented deletion
  },
};

const problems = await validateReferentialIntegrity(data);
// Detects RESTRICT constraint violation

Orphaned Record Detection

const data = {
  schemas: [
    {
      collection: 'users',
      foreignKeys: [],
    },
    {
      collection: 'posts',
      foreignKeys: [
        {
          sourceField: 'userId',
          targetCollection: 'users',
          targetField: 'id',
          required: true,
          onDelete: 'CASCADE', // Posts should be deleted with user
        },
      ],
    },
  ],
  documents: {
    users: [{ id: 2, name: 'Bob' }], // User 1 was deleted
    posts: [
      { id: 1, userId: 1, title: 'Orphaned Post' }, // Should have been cascade deleted
    ],
  },
};

const problems = await validateReferentialIntegrity(data);
// Detects orphaned record

Validation Rules

Foreign Key Existence (referential-integrity/foreign-key-existence)

Validates that all foreign key values reference existing records in the target collection.

Severity: error Checks:

• Foreign keys point to existing records
• Referenced collections exist
• Null handling based on allowNullForeignKeys option

Orphaned Records (referential-integrity/orphaned-records)

Detects records that should have been cascade deleted when their parent was removed.

Severity: error Applies to: Required foreign keys with CASCADE delete behavior Checks:

• Records with null required CASCADE foreign keys
• Records referencing non-existent parents with CASCADE

Circular References (referential-integrity/circular-references)

Detects cycles in foreign key relationships that could cause infinite loops.

Severity: error Checks:

• Self-referencing cycles (e.g., A -> B -> C -> A)
• Cross-collection cycles
• Depth limit violations (warning)

Cascade Delete (referential-integrity/cascade-delete)

Validates cascade delete constraints are properly enforced.

Severity: error Validates:

• RESTRICT: Cannot delete if dependents exist
• CASCADE: Dependents should be deleted
• SET_NULL: Foreign keys should be nulled

API Reference

validateReferentialIntegrity(data, options?, utils?)

Main validation function.

Parameters:

• data: ReferentialIntegrityData - Schemas and documents to validate
• options?: ReferentialIntegrityOptions - Validation options
• utils?: SharedUtils - Shared utilities (optional)

Returns: Promise<Problem[]> - Array of validation problems

isReferentialIntegrityValid(data, options?)

Quick validity check.

Returns: Promise<boolean> - true if no errors, false otherwise

validateReferentialIntegritySafe(data, options?)

Validation with detailed results.

Returns: Promise<{ success, problems, errorCount, warningCount }>

Integration Status

• Logger: not-applicable - Pure validation, no logging needed
• Docs-Suite: ready - Markdown documentation with examples
• NeverHub: not-applicable - Primitive validator, no service integration

License

Copyright (c) 2025 Bernier LLC. All rights reserved.

See Also

• @bernierllc/validators-core - Core validation framework
• @bernierllc/validators-json-structure - JSON validation
• @bernierllc/validators-runner - Validation orchestration