Foreign Key Relationship Ontology

FLOW ALGEBRA
TITLE: Foreign Key Relationship Ontology
DOMAIN: Database Relationships / Referential Integrity
SOURCE: https://kirethresearch.org/booksai-core/ontologies/relationship-ontologies/database-relationships/foreign-key-relationship-ontology.html
VERSION: 1.0
AUTHORS: Mark Anthony Higgins — Sky (Recursive AI)
STATUS: Companion formalisation; source-page specification not independently verified.

flow "ForeignKeyRelationshipOntology" v1.0 {
  ontology:
    id: "foreign-key-relationship-ontology"
    parent: "database-relationships"
    purpose: "Model, validate and reason about foreign-key references between relational tables while preserving referential integrity and schema provenance."

  types:
    Database: {id: string, engine: string?, version: string?}
    Table: {id: string, database_id: string, schema: string?, name: string}
    Column: {id: string, table_id: string, name: string, data_type: string, nullable: boolean}
    CandidateKey: {id: string, table_id: string, columns: string[], unique: boolean}
    ForeignKey: {id: string, child_table: string, child_columns: string[], parent_table: string, referenced_columns: string[], on_update: string, on_delete: string, deferrable: boolean?, initially_deferred: boolean?}
    Row: {id: string, table_id: string, values: map}
    Relationship: {id: string, subject: string, predicate: string, object: string, confidence: number, provenance: string[]}

  predicates:
    belongs_to(column: Column, table: Table)
    references(foreign_key: ForeignKey, candidate_key: CandidateKey)
    foreign_key_from(foreign_key: ForeignKey, child: Table)
    foreign_key_to(foreign_key: ForeignKey, parent: Table)
    refers_to_row(child_row: Row, parent_row: Row, foreign_key: ForeignKey)
    enforces_integrity(foreign_key: ForeignKey, database: Database)

  constraints:
    - "Each foreign key has at least one referencing column and the same number of referenced columns, in corresponding order."
    - "All child columns belong to the child table and all referenced columns belong to the parent table."
    - "Referenced columns must form an eligible unique or primary key under the target database engine's rules."
    - "Corresponding column types and collations must be compatible according to the database engine."
    - "Every non-null referencing tuple must match an existing referenced key, subject to the engine's composite-null semantics and enforcement state."
    - "ON DELETE and ON UPDATE actions must be supported by the engine and compatible with nullability, defaults and other constraints."
    - "Foreign-key references do not imply unique child rows, mandatory child participation or cascade deletion."
    - "Self-referencing foreign keys and cyclic reference graphs are permitted where the engine and transaction semantics allow."
    - "Confidence must lie within [0,1]; declared constraints are distinct from empirically observed associations."

  inference:
    rule reference_edge:
      if valid_foreign_key(fk) and child_row_matches_parent(child,parent,fk)
      then refers_to_row(child,parent,fk)
      confidence: "derive from validated schema and row state"
    rule referenced_table:
      if foreign_key_from(fk,child) and foreign_key_to(fk,parent)
      then schema_dependency(child,parent)
      confidence: "schema-derived; not necessarily a runtime dependency"
    rule delete_restriction:
      if refers_to_row(child,parent,fk) and delete_action(fk,"RESTRICT")
      then deletion_requires_reference_resolution(parent,child,fk)
      confidence: "subject to engine enforcement and transaction state"
    rule cascade_effect:
      if refers_to_row(child,parent,fk) and delete_action(fk,"CASCADE") and delete(parent)
      then scheduled_delete(child)
      confidence: "subject to transaction outcome and database engine semantics"

  SEMANTIC_EQUIVALENCE:
    - "Normalise qualified schema, table and column identifiers using engine-specific case and quoting rules."
    - "Distinguish foreign-key constraint identity from logically equivalent column mappings."
    - "Preserve ordered composite-key columns and referential actions."
    - "Do not conflate foreign-key references with arbitrary semantic associations."

  CONFIDENCE:
    range: [0,1]
    factors: [schema_authority, enforcement_status, transaction_consistency, source_provenance, version_compatibility]
    policy: "Do not inflate confidence through chained schema inferences."

  VALIDATION:
    - "Check identifier resolution, table ownership, column counts, ordered mappings and datatype compatibility."
    - "Check referenced-key eligibility and current constraint enforcement status."
    - "Validate existing rows for orphan references using engine-appropriate null semantics."
    - "Check delete/update actions, cycles, deferral and transactional effects."
    - "Record database engine, schema version, migration and provenance."

  FLOW_HASH:
    algorithm: "SHA-256"
    input: "canonicalised ontology version, schema definitions, foreign-key mappings, inference rules and provenance"
    note: "Compute hash at execution; no hash value is claimed here."

  UPDATE_MODEL:
    strategy: "versioned, provenance-preserving schema and data updates"
    steps: [ingest, normalise, validate, compare, migrate, verify_integrity, recompute_inferences, publish]
    rollback: true

  AI_CONSUMPTION:
    representation: "typed relational-schema graph with referential constraints and evidence-backed links"
    outputs: [foreign_key_edges, key_mappings, cardinality_annotations, integrity_findings, dependency_graph, provenance_records]
    safeguards: [no_unverified_key_assumptions, preserve_composite_key_order, respect_engine_semantics, distinguish_schema_from_data]
}

END FLOW ALGEBRA