Seed Data for Tables with Foreign Key Relationships

Seeding Related Tables

Most databases have tables connected by foreign keys. When generating seed data, the order of insertion and the validity of referenced IDs matter.

Example Schema

CREATE TABLE departments (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  location VARCHAR(100)
);

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL,
  department_id INTEGER NOT NULL REFERENCES departments(id),
  hire_date DATE NOT NULL
);

How Foreign Keys Are Handled

The seed generator treats foreign key columns (department_id) as regular integer columns. When generating values, it uses the column name heuristic for _id-suffixed columns and produces sequential integers starting from 1.

Ensuring Referential Integrity

To maintain valid references, follow these guidelines:

1. Generate the parent table first — departments before employees
2. Match row counts — if you generate 5 departments, make sure department_id values in employees are between 1 and 5
3. Paste tables in dependency order — the tool generates INSERT statements in the order tables appear in the input

Practical Strategy

For a quick approach:

• Generate 10 departments (IDs 1–10)
• Generate 100 employees with department_id values naturally ranging from 1 to a small integer
• The _id heuristic generates sequential values starting from 1, which aligns with the parent table’s auto-increment IDs

Limitations

The generator does not cross-reference tables to validate that every department_id in the employees table matches an existing department. For strict referential integrity in test environments, you may need to manually adjust some values or insert the parent data first and constrain the foreign key range.