Foreign key generators and dependent fields

The simplest way to populate your database with test data.

Foreign key generators and dependent fields

Postby jrichardson » Thu Feb 24, 2011 9:00 am

I'm attempting to use SQL Data Generator to generate some test data for a project but I'm having a few problems.

* If a field has a foreign key, but is marked as nullable, it seems I still need to provide values in the referenced table - is there a way to specify NULL for a FK field?

* I have a foreign key column, where the referenced table has only 1 record. I want to use this value for all of the data that is generated, but I'm getting an error that the generator "could not generate any more values".

* I have 2 fields, one being a start date and one being an end date. Is there a way to ensure the end date is after the start date?

Any help would be much appreciated!
jrichardson
 
Posts: 3
Joined: Fri Jan 07, 2011 1:39 am

Postby chriskelly » Fri Feb 25, 2011 11:54 am

Thank you for your post. I will attempt to answer your points in order:

1. For your nullable foreign key you could select the tickbox "Allow null Values" (under Generator settings) and then the approximate percentage that you wish to be null.

2. Under the Generator settings you could modify the population method to "repeat key values between x and y". Unfortunately, you can only set these up to 100 at the moment. There is an enhancement request SDG-907 to look at improving this though.

3. For your end date column you can set the Column Generator settings with a "Offset from column" range and then select the start date as the column to offset from.
chriskelly
 
Posts: 330
Joined: Mon Apr 19, 2010 1:44 pm
Location: Cambridge, UK

Postby jrichardson » Mon Feb 28, 2011 6:39 am

1. For your nullable foreign key you could select the tickbox "Allow null Values" (under Generator settings) and then the approximate percentage that you wish to be null.
If I have a FK on a column, the "Allow null values" checkbox is grayed out.

2. Under the Generator settings you could modify the population method to "repeat key values between x and y".
I have tried this, e.g. repeat key values between 1 and 10, or repeat key values between 10 and 10, for 10 new records, and it doesn't work.
jrichardson
 
Posts: 3
Joined: Fri Jan 07, 2011 1:39 am

Postby chriskelly » Mon Feb 28, 2011 1:11 pm

In my experience, the "Allow null Values" tickbox is only greyed out when the collumn has been set with "not null", so I would recommend checking this. For the repeat key values, what do you mean "it does not work"?

You can either post your answers here, or sent them to support@red-gate.com. It would be useful for you to send an example schema which we can use to create an object or objects that demonstrate the behaviour that you are seeing.
chriskelly
 
Posts: 330
Joined: Mon Apr 19, 2010 1:44 pm
Location: Cambridge, UK


Return to SQL Data Generator 1

Who is online

Users browsing this forum: No registered users and 0 guests