Foreign Key - Repeat key values at random - doesn't use each value once

Trish

We've been trying to track down a bug in our code, only to discover that it's because the data I've generated using the SQL Data Generator is invalid. I think the Automatic Foreign Key 'Repeat key values at random' option doesn't do what it says it does.

Basically we had a table (let's call it Widgets) and a table of versions (lets call it WidgetVersions), WidgetVersions has a foreign key to the ID of the Widgets table called WidgetID.

In the software, when a Widget is created a corresponding WidgetVersion is created, every time a widget is edited a new entry is made in the WidgetVersions table - so there must be at least one row in the WidgetVersion table for every Widget.

I generated 1000 rows in the Widgets table and 3000 rows in the WidgetVersions table - the WidgetID on the WidgetVersions table is an Automatic Foreign key to the ID on the Widgets table - which I set to "Repeat key values at random".

The help text for "Repeat key values at random" says "Uses the referenced column(s) in a random order. When all values in the referenced column(s) have been used, SQL Data Generator uses them again in a different random order."

I took this to mean that it would populate the WidgetsID column with every value from the ID column of the Widgets table (i.e. 1 to 1000) in a random order, then do it again (1 to 1000) in a random order, then again (1 to 1000) in a random order. So I would have expected each of my 1000 Widgets to have 3 rows in the WidgetVersions table which referenced it. However, I ended up with 23 Widgets with 1 WidgetVersion, 196 Widgets with 2 WidgetVersions, 527 Widgets with 3 WidgetVersions, 251 Widgets with 4 WidgetVersions and 3 Widgets with 0 WidgetVersions (which broke our site).

Is this a bug? Or is the help text "When all values in the referenced column(s) have been used, SQL Data Generator uses them again in a different random order." incorrect?

Thanks,
Trish

way0utwest

The text is wrong in help. It does imply what you said, but that's not true. When you choose random, you get random, not a sequential set of numbers.

I've asked the help text be changed.

Trish

Thanks - that explains it.

In that case, please can I ask you to put in a request to also change the help text for the option above "Repeat key values between n1 and n2 times"? This includes the word 'random' but isn't.

SQL Data Generator selects a value from the referenced column(s) at random, and then repeats the value within the specified number of times in adjacent generated rows.


This solved my problem - as I set it to "Repeat key values between 3 and 3 times" then I get every one of my 1000 foreign keys used exactly 3 times. So that's not 'random' - if we were talking about a music playlist it would be "shuffled".

An option similar to the one above but not 'shuffled' but sequential would also be really useful feature.

Thanks,
Trish