How can we help you today? How can we help you today?

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

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
Trish
0

Comments

2 comments

Sort by
  • 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.
    way0utwest
    0
  • 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
    Trish
    0

Add comment

Please sign in to leave a comment.