Populate tables with test data whilst maintaining relational integrity [closed]

Question

I have a MySQL database with InnoDB tables, many of which have foreign keys.

I was going to write a script to populate the tables with test data (10-20k rows or more) but I thought I ought to ask if there's something already out there that can generate test data based on the field types but ensure relational integrity at the same time?

I've seen and have downloaded the script at generatedata.com but as far as I can see it's clever but it won't read the tables within your db and generate data based on what it finds- you have to do it all manually.

Nagey

Answer 1

This one is as easy as:

call procedurename('DATABASE','TABLE',1000,''); 

will fill 1000 random rows and will populate records as per your needs.

Kettledrummer

Does it respect foreign keys? – Nagey

I tried it out, and I get ERROR 1172 (42000): Result consisted of more than one row :/ – Serosa

Answer 2

EDIT: As pointed out by Mark Byers the Red-Gate tool is SQL Server only.

Red-Gate have their SQL Data Generator which features "Foreign key support for generating consistent data across multiple tables" but it's not cheap.

There is a product from Datanamic called DB Data Generator.

I have not used either of these products but I have used other tools from both companies and found them to be very good.

Haas

I thought SQL Data Generator was for SQL Server only. Will it work with MySQL? – Thuja

Answer 3

I use a seperate connection script. This way, I can create a test database (a duplicate of the real db). I add my test data there.

Then it's one line in the connection script to select the test db, and when I'm ready, one line to change back to the live db.

It's great for playing, designing, testing etc.

Here is an example:

filename: includes/connection.php

<?php

    $DB_USER='username';          
    $DB_PASS='password';             
    $DB_HOST='localhost';                   
    $DB_NAME='dbname'; 

?>

To look at the test db, I change the last line to:

 $DB_NAME='testdbname';

Then, I include this connection file in each PHP script.

Hope this helps.

Artemas