Foreign Key - Repeat key values at random - doesn't use each value once
Trish
Posts: 3 New member
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
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
Tagged:
Best Answer
-
way0utwest
Posts: 312 Rose Gold 1
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.Editor, SQLServerCentral
Answers
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