Foreign Key Dependencies

Foreign Key Dependencies

Understanding and analyzing foreign key relationships and dependencies in D1 databases.

Overview

Foreign Key Dependencies viewer helps you understand table relationships before performing destructive operations. It shows which tables reference each other through foreign key constraints and what will happen when data is deleted.

Key Features:

• Dependency Analysis - Automatic foreign key detection
• Bidirectional View - See both inbound and outbound dependencies
• Row Count Estimates - Know how many rows will be affected
• ON DELETE Behavior - Understand CASCADE, RESTRICT, SET NULL actions
• Visual Indicators - Color-coded by severity
• Mandatory Confirmation - Prevents accidental data loss

Related Features:

• Foreign Key Visualizer - Interactive graph-based constraint management with visual editing
• Cascade Impact Simulator - Detailed cascade preview with multi-format export

What Are Foreign Keys?

Definition

A foreign key is a column (or set of columns) in one table that references the primary key of another table.

Example:

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,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

In this example:

• posts.user_id is a foreign key
• It references users.id
• Enforces referential integrity

Why Foreign Keys Matter

Benefits:

1. Data Integrity - Prevents orphaned records
2. Relationship Enforcement - Maintains valid references
3. Cascade Operations - Automatic cleanup of related data
4. Documentation - Makes relationships explicit

Example Scenario:

If you delete a user, what happens to their posts?
- CASCADE: Posts are automatically deleted
- RESTRICT: Deletion blocked until posts removed
- SET NULL: Posts remain but user_id set to NULL
- NO ACTION: Same as RESTRICT

Dependency Types

Outbound Dependencies

Tables that this table references (via its foreign keys).

Example:

-- posts table has outbound dependency to users
CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

Visualization:

posts → users
(posts references users)

Meaning:

• posts depends on users
• Each post belongs to a user
• Deleting a user may affect posts (depending on ON DELETE)

Inbound Dependencies

Tables that reference this table (have foreign keys pointing to it).

Example:

-- users table has inbound dependency from posts
-- (posts.user_id references users.id)

Visualization:

users ← posts
(users is referenced by posts)

Meaning:

• Other tables depend on users
• Deleting a user may cascade to posts
• Must consider impact on dependent tables

ON DELETE Actions

CASCADE

Behavior: Automatically delete dependent rows when parent is deleted.

Example:

CREATE TABLE comments (
  id INTEGER PRIMARY KEY,
  post_id INTEGER NOT NULL,
  content TEXT,
  FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE
);

Result:

Delete post (id=1) → All comments with post_id=1 are automatically deleted

Use Cases:

• Comments when post is deleted
• Order items when order is deleted
• Related data that has no meaning without parent

Warning:

⚠️ CASCADE deletions are permanent!
Deleting will cascade to 152 rows in 'comments'

RESTRICT

Behavior: Prevent deletion if dependent rows exist.

Example:

CREATE TABLE invoices (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT
);

Result:

Delete user (id=1) → Error: "Cannot delete, invoices exist"
Must delete invoices first, then user

Use Cases:

• Financial records (invoices, payments)
• Audit logs
• Critical data that should never cascade
• Data requiring explicit handling

Warning:

⚠️ RESTRICT constraint prevents deletion
Table 'invoices' will block deletion of user

SET NULL

Behavior: Set foreign key column to NULL when parent is deleted.

Example:

CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  user_id INTEGER,  -- Nullable
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

Result:

Delete user (id=1) → All posts with user_id=1 have user_id set to NULL
Posts remain, but no longer associated with a user

Use Cases:

• Optional relationships
• Historical data (keep record but remove reference)
• Soft deletions
• Data that can exist independently

Warning:

⚠️ SET NULL will orphan 47 posts
Posts will remain but lose user reference

NO ACTION (Default)

Behavior: Similar to RESTRICT (depends on SQLite version).

In SQLite:

• Behaves like RESTRICT in most cases
• Checks constraint at end of statement
• Usually prevents deletion

Example:

CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE NO ACTION
);

Result:

Usually blocks deletion like RESTRICT

Viewing Dependencies

In Delete Dialogs

When attempting to delete a table or row:

Single Table Delete:

┌──────────────────────────────────────────┐
│ Delete Table: users                      │
├──────────────────────────────────────────┤
│ ⚠️  This table has dependencies          │
│                                          │
│ Outbound Dependencies:                   │
│   None                                   │
│                                          │
│ Inbound Dependencies:                    │
│   • posts (CASCADE) - 152 rows           │
│   • comments (CASCADE) - 47 rows         │
│   • sessions (CASCADE) - 3 rows          │
│                                          │
│ Total Impact: 202 rows will be deleted   │
│                                          │
│ □ I understand the impact                │
│                                          │
│ [Cancel] [Delete Table]                  │
└──────────────────────────────────────────┘

Bulk Table Delete:

┌──────────────────────────────────────────┐
│ Delete 3 Tables                          │
├──────────────────────────────────────────┤
│ ▼ users (2 dependencies)                 │
│   Inbound: posts (152 rows CASCADE)      │
│   Inbound: comments (47 rows CASCADE)    │
│                                          │
│ ▼ posts (1 dependency)                   │
│   Outbound: users (references user_id)   │
│                                          │
│ ▶ tags (0 dependencies)                  │
│                                          │
│ □ I understand the dependencies          │
│                                          │
│ [Cancel] [Delete Tables]                 │
└──────────────────────────────────────────┘

Dependency Information Display

Each dependency shows:

⚠️ Table: posts
   Relationship: Inbound (this table is referenced)
   Column: user_id → users.id
   ON DELETE: CASCADE
   Impact: 152 rows will be deleted
   Severity: High (>100 rows)

Color Coding:

• 🔴 Red - RESTRICT (blocks deletion)
• 🟡 Yellow - CASCADE (high impact >50 rows)
• 🔵 Blue - SET NULL (data modified)
• ⚪ Gray - NO ACTION or low impact

Dependency Analysis Process

How It Works

Step 1: Query Foreign Keys

PRAGMA foreign_key_list(table_name);

Returns:

id | seq | table  | from      | to | on_update | on_delete
0  | 0   | users  | user_id   | id | NO ACTION | CASCADE

Step 2: Identify Relationships

• Outbound: Foreign keys in this table
• Inbound: Foreign keys in other tables pointing here

Step 3: Count Affected Rows

SELECT COUNT(*) 
FROM dependent_table 
WHERE foreign_key_column = target_value;

Step 4: Determine Severity

• High: >100 rows, RESTRICT, or deep cascades
• Medium: 20-100 rows
• Low: <20 rows

Step 5: Display Results

• Show in delete dialog
• Color-code by severity
• Require confirmation if dependencies exist

Example Analysis

Target: Delete user (id = 5)

Query 1: Find outbound dependencies

PRAGMA foreign_key_list('users');
-- Result: None (users doesn't reference other tables)

Query 2: Find inbound dependencies

-- Check all tables for foreign keys to users
PRAGMA foreign_key_list('posts');
PRAGMA foreign_key_list('comments');
PRAGMA foreign_key_list('sessions');

Query 3: Count affected rows

SELECT COUNT(*) FROM posts WHERE user_id = 5;      -- 152
SELECT COUNT(*) FROM comments WHERE user_id = 5;   -- 47
SELECT COUNT(*) FROM sessions WHERE user_id = 5;   -- 3

Result Display:

Total Impact: 202 rows across 3 tables
- posts: 152 rows (CASCADE)
- comments: 47 rows (CASCADE)
- sessions: 3 rows (CASCADE)

Row Count Estimates

How Counts Are Calculated

For Row Deletion:

SELECT COUNT(*) 
FROM dependent_table 
WHERE foreign_key_column = ?;

For Table Deletion:

SELECT COUNT(*) 
FROM dependent_table;

Accuracy

Accurate When:

• Simple foreign key relationships
• No triggers modifying behavior
• Foreign keys enabled

May Differ When:

• Triggers perform additional operations
• Complex cascade chains
• Concurrent modifications

Note: Counts are estimates based on current data. Use Cascade Impact Simulator for detailed analysis.

Mandatory Confirmation

When Confirmation Required

Confirmation checkbox appears when:

• ✅ Any dependencies exist
• ✅ CASCADE operations will occur
• ✅ SET NULL operations will affect data
• ✅ RESTRICT constraints prevent deletion

Checkbox Text:

□ I understand that deleting this table/row will affect dependent tables

Why It's Required

Prevents:

• Accidental data loss
• Unintended cascade deletions
• Surprise RESTRICT errors
• Data integrity issues

Ensures:

• User awareness of impact
• Deliberate action
• Informed decision
• Audit trail (user acknowledged)

Delete Button Behavior

Before Confirmation:

[Cancel] [Delete Table] (disabled, grayed out)

After Confirmation:

[Cancel] [Delete Table] (enabled, clickable)

Real-World Examples

Example 1: Blog System

Schema:

CREATE TABLE users (id PRIMARY KEY, name TEXT);
CREATE TABLE posts (id PRIMARY KEY, user_id INTEGER, 
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE);
CREATE TABLE comments (id PRIMARY KEY, post_id INTEGER, user_id INTEGER,
  FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE);

Delete User:

Outbound: None
Inbound:
  - posts → CASCADE (152 rows deleted)
  - comments → CASCADE (89 rows deleted)

Total Impact: 241 rows

Delete Post:

Outbound:
  - users (referenced, not affected by post deletion)
Inbound:
  - comments → CASCADE (47 rows deleted)

Total Impact: 48 rows (post + comments)

Example 2: E-commerce System

Schema:

CREATE TABLE customers (id PRIMARY KEY);
CREATE TABLE orders (id PRIMARY KEY, customer_id INTEGER,
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT);
CREATE TABLE order_items (id PRIMARY KEY, order_id INTEGER,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE);

Delete Customer:

Inbound:
  - orders → RESTRICT (prevents deletion)

❌ Cannot delete customer with orders
Must delete orders first (or change to CASCADE/SET NULL)

Delete Order:

Outbound:
  - customers (referenced, not affected)
Inbound:
  - order_items → CASCADE (5 rows deleted)

Total Impact: 6 rows (order + items)

Example 3: User Profiles

Schema:

CREATE TABLE users (id PRIMARY KEY);
CREATE TABLE profiles (id PRIMARY KEY, user_id INTEGER,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE);
CREATE TABLE preferences (id PRIMARY KEY, user_id INTEGER,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL);

Delete User:

Inbound:
  - profiles → CASCADE (1 row deleted)
  - preferences → SET NULL (1 row modified, user_id = NULL)

Total Impact: 1 deletion, 1 modification

Best Practices

Design Foreign Keys Thoughtfully

Choose appropriate ON DELETE:

• CASCADE: For dependent data (comments, items)
• RESTRICT: For important records (invoices, audit logs)
• SET NULL: For optional relationships (author of post)
• NO ACTION: When uncertain (can change later)

Document Relationships

In schema creation:

-- 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
);

-- User invoices: RESTRICT to prevent accidental deletion of financial data
CREATE TABLE invoices (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT
);

Always Review Dependencies

Before deleting:

1. Check dependency viewer
2. Understand cascade impact
3. Consider using Cascade Impact Simulator
4. Export data if needed
5. Proceed with confirmation

Test in Development First

Workflow:

1. Create test data in development
2. Test deletion scenarios
3. Verify CASCADE behavior
4. Adjust foreign keys if needed
5. Deploy to production

Handle RESTRICT Appropriately

When RESTRICT blocks deletion:

Option 1: Delete dependencies first

DELETE FROM orders WHERE customer_id = 5;
DELETE FROM customers WHERE id = 5;

Option 2: Change foreign key

-- Recreate table with CASCADE instead of RESTRICT

Option 3: Keep the constraint

Maybe RESTRICT is correct - don't delete parent data!

Troubleshooting

Dependencies Not Showing

Cause: Foreign keys not enabled in SQLite.

Check:

PRAGMA foreign_keys;
-- Should return 1 (enabled)

Enable:

PRAGMA foreign_keys = ON;

Note: D1 enables foreign keys by default.

Row Counts Seem Wrong

Cause: Data changed between analysis and display.

Solution:

1. Refresh the dialog
2. Re-run dependency analysis
3. Check for concurrent modifications

RESTRICT Not Working

Cause: Foreign keys not enforced.

Solution:

-- Verify foreign keys enabled
PRAGMA foreign_keys;

-- Check foreign key definition
PRAGMA foreign_key_list(table_name);

Circular Dependencies

Scenario:

-- users references profiles
CREATE TABLE users (
  profile_id INTEGER,
  FOREIGN KEY (profile_id) REFERENCES profiles(id)
);

-- profiles references users
CREATE TABLE profiles (
  user_id INTEGER,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

Result: Both tables depend on each other.

Solution:

• Review design
• Consider breaking circular reference
• Use triggers if necessary
• Document intentional circles

API Access

Get Table Dependencies

GET /api/tables/:dbId/dependencies?tables=users,posts

Response:

{
  "users": {
    "outbound": [],
    "inbound": [
      {
        "table": "posts",
        "column": "user_id",
        "onDelete": "CASCADE",
        "onUpdate": "NO ACTION",
        "rowCount": 152
      }
    ]
  }
}

See API Reference for complete documentation.

Limitations

SQLite-Specific Behavior

• Composite foreign keys supported
• Deferred constraints not available in D1
• ON UPDATE CASCADE not widely used

No View Dependencies

Dependencies to/from views are not analyzed.

Workaround: Check view definitions manually.

Application-Level Relationships

If your app enforces relationships in code (not database), the dependency viewer won't detect them.

Workaround: Document application-level relationships separately.

Quick Navigation

From the dependencies viewer, you can quickly navigate to any referenced or referencing table by clicking the table name. The navigation maintains a breadcrumb trail and applies relevant filters automatically.

How It Works

When viewing dependencies before a table deletion:

1. Click a referenced table name - Navigate to that table
2. Breadcrumb updates - Shows your navigation path
3. Context preserved - You can return to the delete dialog
4. Explore relationships - Verify data before committing to deletion

Use Cases

Verify Dependencies Before Deletion

Before deleting a table:

1. View the dependency analysis
2. Click an inbound dependency table name
3. See which records reference your table
4. Decide if it's safe to delete

Explore Relationship Chains

Starting from one table:

1. View its dependencies
2. Navigate to a referenced table
3. View that table's dependencies
4. Continue exploring the relationship graph

Data Auditing

When investigating data integrity:

1. Start at a suspicious table
2. Click through dependencies to find the root cause
3. Use breadcrumbs to trace back your investigation path

For comprehensive navigation features, see the Foreign Key Navigation guide.

Next Steps

• Foreign Key Navigation - Navigate between tables via FK values
• Foreign Key Visualizer - Visual constraint management and interactive graph
• Cascade Impact Simulator - Detailed cascade analysis
• Table Operations - Delete operations
• Query Console - Create foreign keys
• Database Management - Schema management

---

Need Help? See Troubleshooting or open an issue.