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

> Explore how DrawDB manages relationship creation and foreign key constraints by examining its source code. Learn about object storage, cardinality, and SQL constraint generation.

- Repository: [drawDB/drawdb](https://github.com/drawdb-io/drawdb)
- Tags: deep-dive
- Published: 2026-08-14

---

**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`](https://github.com/drawdb-io/drawdb/blob/main/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`](https://github.com/drawdb-io/drawdb/blob/main/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:

```javascript
// 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`](https://github.com/drawdb-io/drawdb/blob/main/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:

```javascript
// 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`](https://github.com/drawdb-io/drawdb/blob/main/Editor.jsx) and accessed throughout the application. The [`src/utils/utils.js`](https://github.com/drawdb-io/drawdb/blob/main/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:

```javascript
// 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:

```javascript
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`](https://github.com/drawdb-io/drawdb/blob/main/Workspace.jsx), DBML imports via [`applyPlan.js`](https://github.com/drawdb-io/drawdb/blob/main/applyPlan.js), and SQL imports through dialect-specific parsers in `src/utils/importSQL/`
- **Helper utilities**: [`src/utils/utils.js`](https://github.com/drawdb-io/drawdb/blob/main/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`](https://github.com/drawdb-io/drawdb/blob/main/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`](https://github.com/drawdb-io/drawdb/blob/main/Workspace.jsx), while SQL generation always produces standard foreign key constraints regardless of cardinality value.