Generate Test Data With Foreign Keys: Linked Tables That Actually Join
Most fake data generators make every table on its own, so the ids never match and your joins return almost nothing. Here is how to generate linked tables where every foreign key points at a real row, free and in your browser.
Why random test data breaks joins
A foreign key is a column whose values must exist in another table, such as patient_id in a visits table pointing at patient_id in a patients table. When each file is generated separately, the visits get random patient ids that the patients file never contains. Join them and most visits have no patient. These are orphan rows. They hide join bugs, give wrong totals, and make imports fail in databases that enforce foreign keys.
Two ways to get linked tables in Data Utility
The quick way: generate a dataset such as Patients, Orders, Students or Bank accounts, then use the panel under the download buttons. It offers the matching datasets, for example visits, lab results, vitals and prescriptions for patients, or customers, products and employees for orders, with ids that line up.
The flexible way: open the Linked set tab of the Data Generator and build your own set from any group. That is the one to use when the tables you need are in different groups, such as flights from Travel and payments from Finance.
Build a linked set in three steps
1) Add datasets. Press + on a dataset, drag it into your set, or choose a quick set: Hospital, Hospital + claims, Online store, Travel + payments, Bank or School. Datasets the tool knows belong together link themselves.
2) Link columns. Drag the grip beside a column onto a column in another dataset. On a phone use the link button instead. That column will then only use values that exist in the first one, and the parent file is generated first. A link that would make a circle is refused.
3) Download. Pick CSV, Excel, JSON or Parquet and download one ZIP. A report afterwards lists every link, for example 2,000 of 2,000 values found.
Example: flights and payments
Choose the Travel + payments quick set, download the ZIP and save the two files as flights.csv and payments.csv. Open them in SQL on Your Files; each file becomes a table named after the file. First check that no payment is missing its booking:
SELECT COUNT(*) AS payments_without_a_booking
FROM payments p
LEFT JOIN flights f ON f.booking_ref = p.booking_ref
WHERE f.booking_ref IS NULL;It returns 0, because every payment booking_ref was taken from the flights file. Then use the link for real analysis, such as payments by travel class:
SELECT f.travel_class, COUNT(*) AS payments, ROUND(SUM(p.amount), 2) AS total
FROM payments p
JOIN flights f ON f.booking_ref = p.booking_ref
GROUP BY f.travel_class
ORDER BY total DESC;Example: hospital claims that follow the visit
The Hospital + claims quick set goes one step further. Each health insurance claim takes its encounter_id from a real visit and its patient_id from that same visit, so a claim never belongs to a patient who was not at that visit. Check it:
SELECT COUNT(*) AS mismatches
FROM claims c
JOIN visits v ON v.encounter_id = c.encounter_id
WHERE c.patient_id <> v.patient_id;This also returns 0. The claim amounts agree too: the approved amount is never above the billed amount, and a rejected claim pays nothing. For the healthcare columns, see fake patient data.
What if a dataset is not linked yet?
Some datasets, such as the general Insurance claims, have no column that points at another dataset. When you add one, its card shows a yellow hint with one-click buttons such as + patient_id from Patients. Press one and the tool adds the column and links it. Every card also has an Add a column linked to another dataset menu for any other combination.
What it does not do
All values are random and fictional, and linked columns pick parent values evenly, so every parent row has a similar number of children. Amounts and dates in different files are not tied together: a payment amount is not derived from a flight fare, and a visit date is not forced to follow a registration date. A set can hold up to 1,000,000 rows in total, with each dataset up to 500,000. Everything runs in your browser and nothing is uploaded.
Who it helps
QA and test engineers get a database that loads with foreign keys on. Developers get fixtures that behave like production. Analysts and students get tables to practise joins on, for example with the SQL practice questions. If you would rather start from a table definition, the Schema Data Generator builds rows from a CREATE TABLE, and test data without production data explains why not to copy real rows.
Related tool pages
- Fake Data Generator: Generate realistic fake data online: names, emails, addresses, dates and more, up to 500,000 rows, as CSV, JSON, Parquet or Excel. Free.
- Test Data Generator: Generate test data for databases and apps: names, emails, addresses, dates and numbers, up to 500,000 rows, as CSV, JSON, Parquet or Excel. Free.
- Student Data Generator: Fake Student Records: Generate fake student data for class projects: IDs, names, courses, marks and attendance. Download CSV, Excel, JSON or Parquet. Free.
- Student Marks Dataset Generator: Create a student marks dataset by student, subject and exam for SQL, Excel pivot tables and charts. Free, in your browser.
- Sample Data for SQL Practice: Generate sample tables for SQL practice (students, orders, employees), download CSV and query it in your browser. Free.
- Dummy Data for Excel Practice: Generate dummy sales, student or employee data and download Excel to practise filters, pivot tables and VLOOKUP. Free.
Also try
- Fake patient data
- Fake bank and loan test data
- Flight booking sample data
- Fake database for testing
- Free test data generator: all datasets
How to use the Data Generator: step by step
- Open the Data Generator and choose the Linked set tab.
- Add datasets, or pick a quick set such as Hospital + claims.
- Drag a column onto another to link them, or accept the automatic links.
- Download one ZIP and check the report.
When to use the Data Generator
- Fill a test database that enforces foreign keys
- Practise SQL joins on tables that really match
- Build fixtures for an app with orders, customers and products
- Demo a dashboard with linked patients, visits and claims
Example
Input: Dataset: Customers, 3 rows
Output: id,name,email 1,Maya Patel,maya.patel@example.com 2,Leo Fischer,leo.fischer@example.com
Frequently asked questions
How do I generate test data with foreign keys?
Open the Linked set tab of the Data Generator, add the datasets, and drag a column of one onto a column of another. The linked column then only uses values that exist in the parent file. Download everything as one ZIP.
Can I link datasets from different groups?
Yes. You can link any dataset to any other, for example Flight bookings from Travel and Booking payments from Finance, or add a linked column to a dataset that has none.
How do I know the ids really match?
After download the tool shows a check for every link, such as 2,000 of 2,000 found. You can also run a LEFT JOIN check in SQL on Your Files; it should return 0 orphan rows.
Is my data uploaded?
No. The data is generated and zipped in your browser, and all values are random and fictional.
Tip: After you download, load the files in SQL on Your Files and run a LEFT JOIN ... WHERE parent.id IS NULL check. It should return 0.