Generate SQL Data with Referential Integrity (Foreign Keys)

The Foreign Key Problem

Generating mock data for databases with foreign keys is notoriously difficult. Most tools struggle with:

❌

Constraint Violations:

Random data generators create foreign key values that don't exist in parent tables, causing INSERT failures.

❌

Wrong Insert Order:

Inserting child records before parent records breaks foreign key constraints and prevents data import.

❌

Manual Management:

Writing scripts to maintain referential integrity is time-consuming and error-prone, especially with complex schemas.

How MockBlast Solves Foreign Keys

MockBlast was built from the ground up to handle foreign key relationships intelligently:

🔗 Automatic Relationship Detection

Import your CREATE TABLE statements and MockBlast automatically detects foreign key relationships. No manual configuration needed.

✅ Guaranteed Referential Integrity

Every foreign key value is guaranteed to exist in the parent table. No constraint violations, no failed imports.

📋 Correct Insert Order

MockBlast generates INSERT statements in the correct dependency order: parent tables first, then child tables.

🎯 Realistic Distributions

Create realistic data patterns like some users having many orders, while others have few. Configurable relationship distributions.

Example: Multi-Table Schema with Foreign Keys

Import This Schema:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100) UNIQUE
);

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(id),
  title VARCHAR(200),
  content TEXT
);

CREATE TABLE comments (
  id SERIAL PRIMARY KEY,
  post_id INT REFERENCES posts(id),
  user_id INT REFERENCES users(id),
  comment TEXT
);

MockBlast Generates (In Correct Order):

-- Parent table first
INSERT INTO users (id, name, email) VALUES
  (1, 'John Doe', 'john@example.com'),
  (2, 'Jane Smith', 'jane@example.com'),
  (3, 'Bob Johnson', 'bob@example.com');

-- Child table next (only valid user_ids)
INSERT INTO posts (id, user_id, title, content) VALUES
  (1, 1, 'Getting Started', 'Lorem ipsum...'),
  (2, 1, 'Advanced Tips', 'Lorem ipsum...'),
  (3, 2, 'My First Post', 'Lorem ipsum...');

-- Grandchild table last (only valid post_ids and user_ids)
INSERT INTO comments (id, post_id, user_id, comment) VALUES
  (1, 1, 2, 'Great post!'),
  (2, 1, 3, 'Thanks for sharing'),
  (3, 2, 2, 'Very helpful');

Complex Foreign Key Scenarios

🔄 Self-Referencing Tables

Tables that reference themselves (e.g., employees with manager_id):

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  manager_id INT REFERENCES employees(id)
);

MockBlast generates managers first, then assigns them to other employees.

🔗 Many-to-Many Relationships

Junction tables with multiple foreign keys:

CREATE TABLE user_roles (
  user_id INT REFERENCES users(id),
  role_id INT REFERENCES roles(id),
  PRIMARY KEY (user_id, role_id)
);

MockBlast generates valid combinations that exist in both parent tables.

🎯 Composite Foreign Keys

Multi-column foreign keys:

CREATE TABLE order_items (
  order_id INT,
  product_id INT,
  quantity INT,
  FOREIGN KEY (order_id, product_id)
    REFERENCES orders(id, product_id)
);

MockBlast ensures all columns match valid combinations in the parent.

🌐 Multi-Level Hierarchies

Deep relationship chains:

users → orders → items → reviews
      → payments

MockBlast traverses the entire hierarchy and generates data in the correct order.

Why Foreign Keys Matter for Testing

Proper foreign key handling is essential for:

•Database Integrity: Test that your constraints actually work and prevent invalid data.
•Cascade Operations: Test ON DELETE CASCADE and ON UPDATE CASCADE behaviors with realistic data.
•Join Performance: Test JOIN queries with properly related data to measure real-world performance.
•Application Logic: Test your application's handling of relationships (user → posts, orders → items, etc.).
•Migration Testing: Ensure schema changes maintain referential integrity when migrating data.

How to Generate Data with Foreign Keys

1.
Import Your Full Schema: Paste all CREATE TABLE statements including foreign key constraints. MockBlast will parse the relationships automatically.
2.
Review Relationships: MockBlast shows detected foreign key relationships. Verify they're correct or adjust as needed.
3.
Configure Row Counts: Set how many rows to generate for each table. MockBlast ensures child tables have valid foreign keys.
4.
Download Ordered SQL: Get INSERT statements in dependency order, ready to import without constraint violations.