Primary Key Relationship Ontology

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

flow "PrimaryKeyRelationshipOntology" v1.0 {
  ontology:
    id: "primary-key-relationship-ontology"
    parent: "database-relationships"
    purpose: "Represent primary-key selection, uniqueness, entity identification and referential relationships in relational schemas."

  types:
    Table: {id: string, schema: string, name: string}
    Column: {id: string, table_id: string, name: string, datatype: string, nullable: bool}
    Key: {id: string, table_id: string, columns: string[], kind: enum[primary, candidate, alternate, foreign], constraint_name: string?}
    Row: {id: string, table_id: string, values: map}
    Reference: {id: string, source_table: string, source_columns: string[], target_table: string, target_columns: string[], on_delete: string?, on_update: string?}
    Assertion: {id: string, subject: string, predicate: string, object: string, confidence: number, provenance: string[]}

  predicates:
    primary_key_of(key: Key, table: Table)
    comprises(key: Key, column: Column)
    uniquely_identifies(key: Key, row: Row)
    candidate_key_of(key: Key, table: Table)
    references(reference: Reference, target: Key)
    belongs_to(column: Column, table: Table)

  constraints:
    - "Each base table has at most one declared primary-key constraint; a composite primary key may contain several columns."
    - "Every primary-key column belongs to its table and is NOT NULL."
    - "Primary-key tuples are unique across all rows of the table."
    - "Primary-key columns must be nonempty and listed in a stable, explicit order."
    - "A primary key is selected from candidate keys; other candidate keys remain unique alternatives."
    - "Foreign keys may reference a primary key or another eligible unique key, subject to database-engine rules."
    - "Primary-key values are not automatically immutable; updates must honour referencing constraints."
    - "Do not equate matching key values across unrelated tables with identity without a declared mapping."

  inference:
    rule row_identity:
      if primary_key_of(k,t) and row_in(r1,t) and row_in(r2,t) and equal_key_tuple(k,r1,r2)
      then same_relational_row(r1,r2)
      confidence: "valid only within the same table and consistent database snapshot"
    rule key_reference:
      if references(fk,k) and valid_foreign_key_value(fk,r,k)
      then links_to_referenced_row(fk,r,k)
      confidence: "requires constraint satisfaction and consistent snapshot"
    rule composite_uniqueness:
      if primary_key_of(k,t) and composite(k)
      then unique_tuples(k,t)
      confidence: "schema-level constraint, not individual-column uniqueness"

  SEMANTIC_EQUIVALENCE:
    - "Normalise table and column identifiers using qualified schema names and versioned mappings."
    - "Distinguish database row identity, domain entity identity and equivalent identifiers."
    - "Do not infer equivalence between surrogate and natural keys without an explicit mapping."

  CONFIDENCE:
    range: [0,1]
    factors: [schema_authority, constraint_enforcement, snapshot_consistency, provenance_quality, migration_status]
    policy: "Schema-declared constraints are distinguished from empirically inferred uniqueness."

  VALIDATION:
    - "Verify exactly one declared primary key per table where required by the target schema policy."
    - "Reject duplicate primary-key tuples and null components."
    - "Check referenced columns, type compatibility, uniqueness and referential actions."
    - "Check composite key order, migrations and data integrity before publication."

  FLOW_HASH:
    algorithm: "SHA-256"
    input: "canonicalised ontology version, schema declarations, rules and provenance"
    note: "Compute at execution; no precomputed hash is claimed."

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

  AI_CONSUMPTION:
    representation: "typed relational schema graph with constraint-backed edges"
    outputs: [key_definitions, table_links, identity_constraints, integrity_checks, provenance_records]
    safeguards: [no_cross_table_identity_assumption, preserve_composite_keys, enforce_null_and_uniqueness_rules]
}

END FLOW ALGEBRA