Foreign key generators and dependent fields

jrichardson

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!

chriskelly

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.

jrichardson

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.

chriskelly

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.