How DrawDB Manages Relationship Creation and Foreign Key Constraints: A Deep Dive into the Source Code

DrawDB stores relationships as plain JavaScript objects in diagram.relationships, linking tables through field IDs and cardinality enums, then generates SQL ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY statements that preserve update and delete rules.

DrawDB is an open-source database diagramming tool that bridges visual design with executable SQL schema generation. Understanding how it handles relationship creation and foreign key constraints reveals a clean separation between UI interactions, state management, and code generation. This article examines the core implementation in the drawdb-io/drawdb repository, tracing how relationships are created, stored, and exported.

The Relationship Data Model

At the heart of DrawDB's architecture lies a declarative JavaScript object that captures every aspect of a foreign key relationship. This single structure serves as the source of truth for both visualization and SQL generation.

Core Properties of a Relationship Object

Property	Purpose
id	Unique identifier generated via nanoid()
name	Optional constraint name (e.g., fk_user_id_order)
startTableId / endTableId	IDs of the participating tables
startFieldId / endFieldId	IDs of the linked primary/foreign key fields
fields	Array of field-pair objects supporting composite keys
cardinality	Visual styling enum: ONE_TO_ONE, MANY_TO_ONE, ONE_TO_MANY, MANY_TO_MANY
updateConstraint / deleteConstraint	Foreign key action enums from the Constraint type

The fields array deserves special attention—it enables composite foreign keys by allowing multiple column pairs in a single relationship. This design decision in src/utils/utils.js ensures DrawDB can model real-world database schemas without artificial limitations.

Three Paths to Relationship Creation

DrawDB creates relationships through three distinct entry points, all converging on the same state shape.

1. Canvas Drag-and-Drop (UI-Driven)

The Workspace.jsx component captures user interactions on the diagram canvas. When a user drags from one table field to another, the component constructs a temporary relationship object and invokes the context method:

// Simplified flow from src/components/Workspace.jsx
const handleFieldDragEnd = (sourceField, targetField) => {
  const relationship = {
    id: nanoid(),
    startTableId: sourceField.tableId,
    endTableId: targetField.tableId,
    startFieldId: sourceField.id,
    endFieldId: targetField.id,
    fields: [{
      startFieldId: sourceField.id,
      endFieldId: targetField.id
    }],
    cardinality: Cardinality.MANY_TO_ONE, // Default inference
    updateConstraint: Constraint.NO_ACTION,
    deleteConstraint: Constraint.NO_ACTION,
  };
  
  ctx.addRelationship({ relationship }, false);
};

The second parameter false indicates this is a user-initiated creation (not an undo/redo operation).

2. DBML Import (Schema-Driven)

The DBML parser in src/utils/dbml/applyPlan.js processes ref statements and converts them into equivalent relationship objects. This demonstrates DrawDB's commitment to bi-directional transformation—visual models can become DBML, and DBML can become visual models.

3. SQL Import (Legacy-Driven)

The SQL import parsers located in src/utils/importSQL/*.js (PostgreSQL, MySQL, SQLite variants) perform the most complex transformation. These parsers analyze FOREIGN KEY clauses in existing CREATE TABLE or ALTER TABLE statements and reconstruct the relationship metadata:

// Conceptual excerpt from src/utils/importSQL/postgres.js
function extractForeignKey(tableName, constraintDef) {
  const match = constraintDef.match(/FOREIGN KEY \(([^)]+)\) REFERENCES (\w+)\(([^)]+)\)/i);
  if (!match) return null;
  
  const [, localColumns, refTable, refColumns] = match;
  // Parse ON UPDATE / ON DELETE actions
  const updateAction = extractAction(constraintDef, 'UPDATE');
  const deleteAction = extractAction(constraintDef, 'DELETE');
  
  return {
    name: constraintName,
    startTableId: resolveTableId(refTable),
    endTableId: resolveTableId(tableName),
    // ... field mappings
    updateConstraint: updateAction,
    deleteConstraint: deleteAction,
  };
}

State Management and Relationship Utilities

Once created, relationships live in the reactive state managed by Editor.jsx and accessed throughout the application. The src/utils/utils.js file provides essential helper functions:

• getRelationshipFields(relationship, tables) — Resolves IDs to actual field objects
• isFieldRelatedToTable(fieldId, tableId, relationships) — Checks field participation
• getVisibleFields(table, relationships, allTables) — Computes which fields to render based on relationship visibility

These utilities ensure that relationship data remains normalized (stored by ID) while components receive denormalized data for rendering.

Exporting Relationships to SQL Foreign Keys

The src/utils/exportSQL/*.js modules reverse the import process, iterating over diagram.relationships to generate dialect-specific ALTER TABLE statements:

// From src/utils/exportSQL/postgres.js
const generateForeignKeySQL = (relationship, tableMap) => {
  const endTable = tableMap.get(relationship.endTableId);
  const startTable = tableMap.get(relationship.startTableId);
  
  const localCols = relationship.fields
    .map(f => escapeIdentifier(getFieldName(endTable, f.endFieldId)))
    .join(', ');
    
  const refCols = relationship.fields
    .map(f => escapeIdentifier(getFieldName(startTable, f.startFieldId)))
    .join(', ');

  return `ALTER TABLE ${escapeIdentifier(endTable.name)}
    ADD CONSTRAINT ${escapeIdentifier(relationship.name || `fk_${nanoid(6)}`)}
    FOREIGN KEY (${localCols})
    REFERENCES ${escapeIdentifier(startTable.name)} (${refCols})
    ON UPDATE ${relationship.updateConstraint}
    ON DELETE ${relationship.deleteConstraint};`;
};

This generation respects the cardinality by determining which table holds the foreign key (the "many" side in 1:N relationships), though the actual constraint enforcement relies on the database engine.

Programmatic Relationship Creation

For automation or plugin development, relationships can be constructed and injected directly:

import { nanoid } from 'nanoid';
import { ctx } from '@drawdb/context';
import { Cardinality, Constraint } from '@drawdb/enums';

const compositeForeignKey = {
  id: nanoid(),
  name: 'fk_order_customer_composite',
  startTableId: 'tbl_customer',
  endTableId: 'tbl_order',
  fields: [
    { startFieldId: 'fld_customer_id', endFieldId: 'fld_order_customer_id' },
    { startFieldId: 'fld_customer_region', endFieldId: 'fld_order_region' }
  ],
  cardinality: Cardinality.MANY_TO_ONE,
  updateConstraint: Constraint.CASCADE,
  deleteConstraint: Constraint.SET_NULL,
};

ctx.addRelationship({ relationship: compositeForeignKey }, false);

Summary

• Unified data model: DrawDB represents every relationship as a JavaScript object with IDs, fields, cardinality, and constraint actions, stored in diagram.relationships
• Multiple creation paths: Canvas interactions in Workspace.jsx, DBML imports via applyPlan.js, and SQL imports through dialect-specific parsers in src/utils/importSQL/
• Helper utilities: src/utils/utils.js provides field resolution and visibility calculations through functions like getRelationshipFields and isFieldRelatedToTable
• Bidirectional transformation: The same relationship objects drive both visual rendering and SQL generation in src/utils/exportSQL/, ensuring schema fidelity
• Composite key support: The fields array structure accommodates multi-column foreign keys without schema changes

Frequently Asked Questions

How does DrawDB handle composite foreign keys?

DrawDB's relationship model includes a fields array where each element contains startFieldId and endFieldId pairs. When generating SQL, the exporter concatenates all field pairs into a single FOREIGN KEY (col1, col2) clause. This design is consistent across import, storage, and export pipelines.

Can DrawDB preserve existing foreign key constraint names during import?

Yes. The SQL import parsers extract CONSTRAINT clause names and store them in the name property of the relationship object. When exporting, if no name exists, DrawDB generates one using nanoid(6) to ensure valid SQL identifiers.

What happens to relationships when a referenced table is deleted?

The src/utils/utils.js utility isFieldRelatedToTable and related validation logic help maintain referential integrity in the diagram state. However, the actual database enforcement depends on the generated SQL's ON DELETE action—CASCADE, SET NULL, RESTRICT, or NO ACTION—which is preserved from the original relationship definition.

Where is the cardinality visual styling determined?

Cardinality affects arrow rendering in the canvas but does not change the SQL output directly. The Cardinality enum values in the relationship object inform the SVG path generation in Workspace.jsx, while SQL generation always produces standard foreign key constraints regardless of cardinality value.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}