Skip to content

Foreign Key Visualizer

Jump to bottom
Temp edited this page Nov 30, 2025 · 3 revisions

Foreign Key Visualizer / Editor

Interactive graph-based foreign key relationship management with visual editing capabilities.

Note: For read-only schema visualization and documentation, see ER Diagram. This page covers the interactive editor for adding, modifying, and deleting foreign key constraints.

Overview

The Foreign Key Visualizer/Editor provides a comprehensive visual interface for managing foreign key constraints in your D1 databases. Using an interactive graph powered by ReactFlow, you can see all table relationships at a glance, add new constraints, modify existing ones, and delete relationships - all through an intuitive visual interface.

Key Features:

  • Interactive Graph Visualization - Pan, zoom, and explore table relationships
  • Dual Layout System - Switch between hierarchical and force-directed layouts
  • Add Foreign Keys - Create new constraints with guided validation
  • Modify Constraints - Edit ON DELETE and ON UPDATE behaviors
  • Delete Constraints - Remove foreign key relationships safely
  • Type Validation - Automatic column type compatibility checking
  • Orphan Detection - Prevents adding FKs that would violate referential integrity
  • Color-Coded Edges - Visual distinction between CASCADE, RESTRICT, SET NULL, and NO ACTION
  • Table Filtering - Focus on specific tables and their relationships
  • Column Display - Shows table columns with types and primary key indicators

Accessing the Visualizer

In DatabaseView

The Foreign Key Visualizer is accessible within the Relationships tab:

  1. Navigate to a database
  2. Click the "Relationships" tab (alongside "Tables" and "Query Builder")
  3. Ensure "Foreign Key Editor" is selected in the view toggle (default)
  4. The graph loads automatically showing all foreign key relationships

Tab Icon: Network/Graph icon
Location: Database-level navigation tabs
View Toggle: "Foreign Key Editor" (editable) vs "ER Diagram" (read-only)

Graph Visualization

Layout Modes

Hierarchical Layout (Default):

Top-to-bottom hierarchical arrangement using dagre algorithm
- Parent tables at top
- Child tables below
- Clear dependency flow
- Optimal for understanding cascade direction

Force-Directed Layout:

Physics-based layout with circular arrangement
- Tables positioned by relationship strength
- Natural clustering of related tables
- Better for densely connected schemas
- Interactive repositioning

Switching Layouts:

  • Use the layout selector in the toolbar
  • Graph automatically re-arranges
  • Node positions calculated dynamically

Node Representation

Each table is displayed as a node with:

┌─────────────────────────┐
│ table_name              │ (Table name in bold)
│ 152 rows                │ (Row count)
├─────────────────────────┤
│ id          INTEGER (PK)│ (Columns with types)
│ user_id     INTEGER     │
│ title       TEXT        │
│ content     TEXT        │
└─────────────────────────┘

Node Information:

  • Table Name - Bold text at top
  • Row Count - Total rows in table
  • Columns - Name, type, and PK indicator
  • Border Color - Primary color scheme

Node Interactions:

  • Click - Select node (optional callback to navigate to table)
  • Drag - Reposition in graph (force-directed mode)
  • Hover - Highlight connections

Edge Representation

Edges represent foreign key constraints with visual indicators:

Edge Colors (by ON DELETE action):

  • 🟡 Yellow - CASCADE (data will be deleted)
  • 🔴 Red - RESTRICT/NO ACTION (blocks deletion)
  • 🔵 Blue - SET NULL/SET DEFAULT (data modified)
  • ⚪ Gray - Default/no action

Edge Labels:

  • Shows ON DELETE action (if not NO ACTION)
  • Positioned along edge path
  • Color matches edge

Edge Styles:

  • Solid Line - Standard constraint
  • Dashed Line - ON UPDATE is not NO ACTION (future enhancement)
  • Arrow - Points from source to target table

Example:

posts ──CASCADE──> users
(posts.user_id references users.id)

Interactive Features

Pan & Zoom:

  • Mouse Wheel - Zoom in/out
  • Click & Drag - Pan across graph

Tip: Click on a relationship line (edge) to edit or delete a constraint.

Background:

  • Dotted grid pattern
  • Helps with alignment
  • Visible at all zoom levels

Adding Foreign Keys

Opening the Editor

From Toolbar:

  1. Click "Add Foreign Key" button in top-left panel
  2. Editor dialog opens

Editor Layout:

┌─────────────────────────────────────┐
│ Add Foreign Key                     │
├─────────────────────────────────────┤
│ Constraint Name (optional)          │
│ [fk_source_column]                  │
│                                     │
│ Source Table       [Select ▼]       │
│ Source Column      [Select ▼]       │
│                                     │
│ References Table   [Select ▼]       │
│ References Column  [Select ▼]       │
│                                     │
│ ON DELETE          [Select ▼]       │
│ ON UPDATE          [Select ▼]       │
│                                     │
│ [Cancel]           [Add Foreign Key]│
└─────────────────────────────────────┘

Step-by-Step Process

1. Select Source Table

  • Dropdown lists all tables in database
  • Select the table that will contain the foreign key

2. Select Source Column

  • Dropdown lists all columns in selected source table
  • Shows column type for reference
  • Example: user_id (INTEGER)

3. Select Target Table (References)

  • Dropdown lists all tables in database
  • This is the table being referenced
  • Example: users

4. Select Target Column (References)

  • Dropdown lists all columns in selected target table
  • Shows column type and PK indicator
  • Example: id (INTEGER) (PK)

5. Choose ON DELETE Action

  • NO ACTION (default) - Blocks deletion if references exist
  • CASCADE - Automatically delete dependent rows
  • RESTRICT - Explicitly blocks deletion
  • SET NULL - Set foreign key column to NULL
  • SET DEFAULT - Set foreign key column to default value

6. Choose ON UPDATE Action

  • Same options as ON DELETE
  • Typically left as NO ACTION

7. Optional Constraint Name

  • Auto-generated: fk_sourcetable_sourcecolumn
  • Customize if desired
  • Used for modification and deletion

8. Add Foreign Key

  • Click "Add Foreign Key" button
  • Backend validates and creates constraint
  • Graph refreshes automatically

Validation Rules

The backend performs comprehensive validation before creating the foreign key:

Column Type Compatibility:

✓ INTEGER → INTEGER
✓ TEXT → TEXT
✓ REAL → REAL
✗ INTEGER → TEXT (incompatible)
✗ TEXT → INTEGER (incompatible)

Target Column Requirements:

✓ Target column is PRIMARY KEY
✓ Target column has UNIQUE constraint
✗ Target column is not unique (rejected)

Orphan Row Detection:

✓ All source values exist in target table
✗ Orphaned rows detected (rejected with count)

Validation Errors:

  • Display in red error box
  • Clear error message
  • Specific guidance on resolution

Example Error:

❌ Cannot add foreign key: 
Column type mismatch (INTEGER vs TEXT)

Example Warning:

❌ Cannot add foreign key:
Would create 15 orphaned rows.
Target column values not found in reference table.

Modifying Foreign Keys

Selecting a Constraint

1. Click an Edge

  • Click any edge (relationship line) in the graph
  • Details panel appears in bottom-right corner

Details Panel:

┌─────────────────────────────┐
│ Foreign Key Details         │
├─────────────────────────────┤
│ posts.user_id → users.id    │
│                             │
│ ON DELETE: CASCADE          │
│ ON UPDATE: NO ACTION        │
│                             │
│ [Edit]           [Delete]   │
└─────────────────────────────┘

2. Click Edit Button

  • Opens Foreign Key Editor in modify mode
  • Source/target tables and columns are read-only
  • ON DELETE and ON UPDATE are editable

Modification Dialog

Read-Only Fields:

  • Constraint Name (shown for reference)
  • Source Table (grayed out)
  • Source Column (grayed out)
  • References Table (grayed out)
  • References Column (grayed out)

Editable Fields:

  • ON DELETE action (dropdown)
  • ON UPDATE action (dropdown)

Process:

  1. Modify ON DELETE or ON UPDATE
  2. Click "Save Changes"
  3. Backend recreates table with new constraint
  4. Graph refreshes automatically

Example Modification:

Change: CASCADE → RESTRICT
Result: Deletions now blocked if references exist

SQLite Implementation

Behind the Scenes:

Foreign key modification in SQLite requires table recreation:

  1. Get Current Schema - Fetch CREATE TABLE statement
  2. Parse Foreign Keys - Identify constraint to modify
  3. Generate New Schema - Update ON DELETE/ON UPDATE
  4. Create Temp Table - New structure with modified constraint
  5. Copy Data - Transfer all rows to temp table
  6. Drop Original - Remove old table
  7. Rename Temp - Rename temp to original name
  8. Recreate Indexes - Restore all indexes

Note: This is transparent to the user but may take a few seconds for large tables.

Deleting Foreign Keys

Deletion Process

1. Select Edge

  • Click the edge representing the constraint
  • Details panel appears

2. Click Delete Button

  • Confirmation dialog opens

Confirmation Dialog:

┌─────────────────────────────────────┐
│ Delete Foreign Key?                 │
├─────────────────────────────────────┤
│ Are you sure you want to delete     │
│ the foreign key constraint between  │
│ posts.user_id and users.id?         │
│                                     │
│ This action cannot be undone.       │
│                                     │
│ [Cancel]           [Delete FK]      │
└─────────────────────────────────────┘

3. Confirm Deletion

  • Click "Delete Foreign Key"
  • Backend removes constraint via table recreation
  • Graph refreshes automatically

Result:

  • Edge disappears from graph
  • Constraint removed from table schema
  • Referential integrity no longer enforced

When to Delete Foreign Keys

Valid Reasons:

  • Relationship no longer needed
  • Changing schema design
  • Constraint causing issues
  • Preparing for data migration

Caution:

  • Data integrity no longer enforced
  • Orphaned rows may occur
  • Consider implications before deleting

Real-World Examples

Example 1: Blog System

Schema:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL,
  title TEXT NOT NULL
);

CREATE TABLE comments (
  id INTEGER PRIMARY KEY,
  post_id INTEGER NOT NULL,
  user_id INTEGER NOT NULL,
  content TEXT
);

Adding Foreign Keys via Visualizer:

Step 1: posts → users

  • Source: posts table, user_id column
  • Target: users table, id column
  • ON DELETE: CASCADE (delete posts when user deleted)
  • ON UPDATE: NO ACTION

Step 2: comments → posts

  • Source: comments table, post_id column
  • Target: posts table, id column
  • ON DELETE: CASCADE (delete comments when post deleted)
  • ON UPDATE: NO ACTION

Step 3: comments → users

  • Source: comments table, user_id column
  • Target: users table, id column
  • ON DELETE: CASCADE (delete comments when user deleted)
  • ON UPDATE: NO ACTION

Resulting Graph:

       users
      /     \
   CASCADE  CASCADE
    /         \
  posts     comments
    |
  CASCADE
    |
  comments

Example 2: E-commerce System

Schema:

CREATE TABLE customers (
  id INTEGER PRIMARY KEY
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL
);

CREATE TABLE order_items (
  id INTEGER PRIMARY KEY,
  order_id INTEGER NOT NULL,
  product_id INTEGER NOT NULL
);

CREATE TABLE products (
  id INTEGER PRIMARY KEY
);

Adding Foreign Keys:

  1. orders → customers (RESTRICT)

    • Prevents customer deletion if orders exist
    • Protects financial data

  2. order_items → orders (CASCADE)

    • Delete items when order deleted
    • Maintains consistency

  3. order_items → products (RESTRICT)

    • Prevents product deletion if in orders
    • Data integrity

Resulting Graph:

customers ←RESTRICT─ orders ─CASCADE→ order_items ─RESTRICT→ products

Example 3: Modifying Cascade Behavior

Scenario: Change user deletion from CASCADE to RESTRICT

Original:

FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE

Process:

  1. Click edge between posts and users
  2. Click Edit in details panel
  3. Change ON DELETE from CASCADE to RESTRICT
  4. Click Save Changes

New Behavior:

FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT

Result:

  • User deletion now blocked if posts exist
  • Must delete posts first before deleting user
  • More safety, less automation

Advanced Features

Table Filtering (Future Enhancement)

Concept:

  • Enter table name to filter graph
  • Only show selected table and direct dependencies
  • Useful for complex schemas

Column Display Toggle

Current: All columns shown in nodes Future: Toggle to show only FK columns

Relationship Metadata

Shown in Details Panel:

  • Constraint name
  • Source/target tables and columns
  • ON DELETE and ON UPDATE actions
  • Row counts (from source table)

Export Relationships

Future Enhancement:

  • Export graph as PNG/SVG
  • Export relationships as JSON
  • Generate documentation

API Endpoints

Get All Foreign Keys

GET /api/tables/:dbId/foreign-keys

Response:

{
  "result": {
    "nodes": [
      {
        "id": "users",
        "label": "users",
        "columns": [
          {"name": "id", "type": "INTEGER", "isPK": true},
          {"name": "name", "type": "TEXT", "isPK": false}
        ],
        "rowCount": 152
      },
      {
        "id": "posts",
        "label": "posts",
        "columns": [
          {"name": "id", "type": "INTEGER", "isPK": true},
          {"name": "user_id", "type": "INTEGER", "isPK": false},
          {"name": "title", "type": "TEXT", "isPK": false}
        ],
        "rowCount": 487
      }
    ],
    "edges": [
      {
        "id": "fk_posts_user_id_users_id",
        "source": "posts",
        "target": "users",
        "sourceColumn": "user_id",
        "targetColumn": "id",
        "onDelete": "CASCADE",
        "onUpdate": "NO ACTION"
      }
    ]
  },
  "success": true
}

Add Foreign Key

POST /api/tables/:dbId/foreign-keys/add
Content-Type: application/json

Body:

{
  "sourceTable": "posts",
  "sourceColumn": "user_id",
  "targetTable": "users",
  "targetColumn": "id",
  "onDelete": "CASCADE",
  "onUpdate": "NO ACTION",
  "constraintName": "fk_posts_user_id"
}

Response:

{
  "result": {
    "message": "Foreign key constraint added successfully"
  },
  "success": true
}

Modify Foreign Key

PATCH /api/tables/:dbId/foreign-keys/:constraintName
Content-Type: application/json

Body:

{
  "onDelete": "RESTRICT",
  "onUpdate": "NO ACTION"
}

Response:

{
  "result": {
    "message": "Foreign key constraint modified successfully"
  },
  "success": true
}

Delete Foreign Key

DELETE /api/tables/:dbId/foreign-keys/:constraintName

Response:

{
  "result": {
    "message": "Foreign key constraint deleted successfully"
  },
  "success": true
}

See API Reference for complete API documentation.

Best Practices

Design Relationships Thoughtfully

Choose appropriate ON DELETE:

  • CASCADE - For dependent data (comments, order items)
  • RESTRICT - For important records (invoices, audit logs)
  • SET NULL - For optional relationships (nullable foreign keys)
  • NO ACTION - When uncertain (can change later)

Use the Visualizer for Schema Understanding

Before Making Changes:

  1. Open the visualizer
  2. Understand existing relationships
  3. Plan new constraints
  4. Consider cascade impact

During Development:

  • Add constraints as you build
  • Visualize relationships immediately
  • Test cascade behavior
  • Adjust as needed

Document Constraint Decisions

Constraint Names:

  • Use descriptive names
  • Include table and column
  • Example: fk_posts_user_id not fk1

Comments in Schema:

-- User posts: CASCADE because posts have no meaning without user
CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

Test Cascade Behavior

Workflow:

  1. Add constraint in visualizer
  2. Test deletion in development
  3. Use Cascade Impact Simulator for preview
  4. Verify expected behavior
  5. Deploy to production

Combine with Dependency Analysis

Use Both Tools:

Troubleshooting

Graph Not Loading

Possible Causes:

  • No tables in database
  • No foreign key constraints defined
  • Network error

Solution:

  • Check that tables exist
  • Verify foreign keys with PRAGMA foreign_key_list(table_name)
  • Click Refresh button in toolbar

Cannot Add Foreign Key

Common Errors:

Type Mismatch:

❌ Column type mismatch (INTEGER vs TEXT)
Solution: Ensure source and target columns have compatible types

Orphaned Rows:

❌ Would create 15 orphaned rows
Solution: Clean up data first or remove/update orphaned values

Target Column Not Unique:

❌ Target column must be PRIMARY KEY or have UNIQUE constraint
Solution: Add UNIQUE constraint or use a different column

Modification Taking Long Time

Cause: Large table requires recreation

What's Happening:

  • SQLite is recreating table with new constraint
  • Copying all data to temporary table
  • May take 10-30 seconds for large tables (>100k rows)

Solution:

  • Wait for operation to complete
  • Avoid modifying very large tables during peak hours

Edge Not Visible

Cause: Node overlap or zoom level

Solution:

  • Zoom in/out to see all edges
  • Switch to hierarchical layout
  • Click on node to highlight connections

Limitations

SQLite Constraints

Cannot Change:

  • Source table/column (must delete and recreate)
  • Target table/column (must delete and recreate)

Can Change:

  • ON DELETE action (via modification)
  • ON UPDATE action (via modification)

Performance Considerations

Large Tables:

  • Table recreation may be slow (>100k rows)
  • Consider maintenance windows
  • Test with smaller datasets first

Many Relationships:

  • Graph may become cluttered (>50 tables)
  • Use table filtering (future enhancement)
  • Consider breaking into multiple databases

No Composite Foreign Keys

Current: UI supports single-column foreign keys only Workaround: Create composite keys via Query Console

Related Features

Next Steps

Need Help? See Troubleshooting or open an issue.

D1 Manager Wiki

🚀 Getting Started

📚 Core Features

🔗 Relationships

🔍 Search & Analysis

📊 Observability

🔧 Advanced Features

⚙️ Development

🌐 Deployment

📖 Reference

Clone this wiki locally