How to Generate Unique Emails & Usernames for SQL Testing

The Unique Constraint Problem

UNIQUE constraints are essential for data integrity, but they make generating mock data challenging:

❌

Duplicate Key Violations:

Random email generators often create duplicates like "john.doe@example.com" multiple times, causing INSERT failures.

❌

Import Failures:

Attempting to import data with duplicate emails or usernames stops the entire import process, wasting time.

❌

Manual Deduplication:

Manually ensuring uniqueness across thousands of rows is tedious and error-prone.

How MockBlast Guarantees Uniqueness

MockBlast was built to handle UNIQUE constraints intelligently:

✅ Automatic Constraint Detection

Import your CREATE TABLE statement and MockBlast automatically detects UNIQUE constraints. It knows which columns must have unique values.

🔢 Multiple Uniqueness Strategies

Choose how to ensure uniqueness: sequential numbers (john.doe.1@example.com), UUIDs (john.doe.a1b2c3@example.com), or timestamps.

📋 Value Tracking

MockBlast tracks all generated values to prevent duplicates, even when generating millions of rows across multiple tables.

🎯 Realistic Results

Generated values look realistic while being guaranteed unique. Test your application with data that resembles production.

Example: Multiple UNIQUE Constraints

Import This Schema:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) UNIQUE NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,
  phone VARCHAR(20) UNIQUE,
  created_at TIMESTAMP DEFAULT NOW()
);

MockBlast Generates (All Values Unique):

INSERT INTO users (id, username, email, phone, created_at) VALUES
  (1, 'johndoe', 'john.doe@example.com', '+1-555-0101', '2024-01-15 10:30:00'),
  (2, 'janesmith', 'jane.smith@example.com', '+1-555-0102', '2024-01-16 14:20:00'),
  (3, 'bobjohnson', 'bob.johnson@example.com', '+1-555-0103', '2024-01-17 08:15:00'),
  (4, 'alicewilliams', 'alice.williams@example.com', '+1-555-0104', '2024-01-18 09:45:00'),
  (5, 'charliebrownz', 'charlie.brown@example.com', '+1-555-0105', '2024-01-19 16:30:00');

-- All usernames, emails, and phone numbers are guaranteed unique!
-- No duplicate key violations, import succeeds 100% of the time.

Uniqueness Strategies

🔢 Sequential Numbers

Append sequential numbers to base values:

john.doe.1@example.com
john.doe.2@example.com
john.doe.3@example.com

✓ Simple and predictable
✓ Works for millions of rows
✓ Easy to understand patterns

🎲 UUID Suffixes

Append short UUID segments:

john.doe.a1b2@example.com
jane.smith.c3d4@example.com
bob.johnson.e5f6@example.com

✓ Looks more realistic
✓ Harder to predict
✓ Good for demo data

📅 Timestamp Suffixes

Append timestamps or dates:

john.doe.20240115@example.com
jane.smith.20240116@example.com
bob.johnson.20240117@example.com

✓ Conveys chronological info
✓ Realistic for time-based data
✓ Easy to sort

🎯 Smart Combinations

Combine strategies for variety:

john.doe@example.com
johnathan.doe@example.com
j.doe@example.com
john.d@example.com

✓ Most realistic looking
✓ Varied patterns
✓ Great for UI testing

Testing UNIQUE Constraints

MockBlast's unique value generation is perfect for testing database constraints:

•Constraint Validation: Verify that UNIQUE constraints work correctly by attempting to insert duplicates manually.
•Index Performance: Test UNIQUE index performance with millions of unique values to ensure fast lookups.
•Validation Logic: Test application-level email and username validation with diverse but unique values.
•Error Handling: Test how your app handles duplicate key errors when users try to register with existing emails.
•Composite Uniqueness: Test UNIQUE constraints across multiple columns (e.g., UNIQUE(first_name, last_name, company_id)).

Common Unique Value Use Cases

📧 User Registration Testing

Test user registration flows with thousands of unique emails. Verify that duplicate email detection works correctly.

🏢 Multi-Tenant Systems

Generate unique tenant slugs, domain names, and identifiers for multi-tenant applications.

🔑 API Key Generation

Create unique API keys, tokens, and identifiers for testing authentication and authorization systems.

📦 Product SKUs

Generate unique product SKUs, order numbers, and tracking codes for e-commerce testing.

How to Generate Unique Values

1.
Import Your Schema: Paste your CREATE TABLE statement with UNIQUE constraints into MockBlast.
2.
Verify Constraints: MockBlast automatically detects UNIQUE constraints on email, username, and other columns.
3.
Choose Strategy: Select how to ensure uniqueness (sequential, UUID, timestamp, or smart combinations).
4.
Download SQL: Get INSERT statements with guaranteed unique values, ready to import without duplicate key errors.