Red Gate forums :: View topic - Generate composite key from 2 Foreign keys
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Data Generator 1
SQL Data Generator 1 forum

Generate composite key from 2 Foreign keys

Search in SQL Data Generator 1 forum
Post new topic   Reply to topic
Jump to:  
Author Message
Aline



Joined: 17 Apr 2008
Posts: 12

PostPosted: Thu Apr 17, 2008 1:12 pm    Post subject: Generate composite key from 2 Foreign keys Reply with quote

Hello

I've got the following problem:

I've got 3 tables:
A with 250 records
B with with 100 records
and C's Primary Key consists of a Foreign Key (FK) to A and a FK to B and should contain about 5'000 records (out of 25'000 possibilities).

How can I do this? With any "Repeat..." on both FK columns it aborts with the message "Violation fo PRIMARYKEY..." and inserts none at all in table C.
(In Beta version it worked partly: After some records (about 180) it stopped with the message - although i chose to skip row on invalid data)

Thanks for your help
Aline
Back to top
View user's profile Send private message
Lionel



Joined: 16 Dec 2005
Posts: 170

PostPosted: Thu Apr 17, 2008 7:06 pm    Post subject: Reply with quote

Sorry for taking so long to get back to you. In version 1.0 we have added an extra feature to SQL Data Generator that makes this situation work a bit better. What happend is if you have a unique constraint across several columns we detect that each of the columns do not have to be unique so we restart the generators. I will try to explain this a bit better but it is very hard to explain without a piece of paper :). Feel free to skip this bit if you just want the fix for your problem :).

If we have a generator that gives the values (a,b,c) and another generator that produces the values (1,2) and we have a unique constraint across two columns in the beta the follwoing would have happened

Col1 Col2
a 1
b 2

Then the generator on Col2 will run out of values so we only generate 2 values which is clearly incorrect as we can generate 6 values. So in the released version we detect that we can repeat the values in the generator as long as you do not get two rows the sames. I will not explain the algorithm here as it is not very interesting but what you should end up with in SDG version 1.0 is.

Col1 Col2
a 1
b 2
c 1
a 2
b 1
c 2

Which will give you all the possible values. So why is this not working for you. Well there is a bug in the current version (1.0) where it is not detecting that it can do the column restarts correctly. I will email you a patch that fixes this bug and if anyone else has this problem then please send me an email and I will give you the patch. It will be fixed in the next patch release. One important point to add is that all the generators that are assigned to columns that are part of the unique constraint must be set to "All Key Values are Unique" or the columns will not get restarted.

Now as you have noticed the skip rows feature does not detect duplicates and skip them. Unfortinatly we chouse a very poor name for this feature. This feature infact only detects when values are the wrong type or too wide from a column and skips thouse rows. I will definatly look at extending this feature to skip duplicate rows too I agree that it would make sense if it skipped rows that violated constraints were duplicate rows too. So sorry for our very poor naming of this feature. As a work around you can use the IGNORE_DUP_KEY when createing unqiue index and then the duplicate rows that SQL Data Generator produces will just silently get dropped and not stop the generation. It is a bit of a nasty hack but will work.

I hope that clarifies everything but if you have any more questions please ask as I do not think that was my best explanation ever.

Lionel
_________________
Software Engineer
Red Gate Software
Back to top
View user's profile Send private message
Aline



Joined: 17 Apr 2008
Posts: 12

PostPosted: Mon Apr 21, 2008 7:51 am    Post subject: Reply with quote

Very Happy
This patch works wonderfully.

Thank you.

Aline
Back to top
View user's profile Send private message
Aline



Joined: 17 Apr 2008
Posts: 12

PostPosted: Fri Jul 04, 2008 9:35 am    Post subject: I've got the same problem again with Version 1.1 Reply with quote

Currently, I use V1.1.0.63.
... and I got the problem again.

Is there another patch?

Thanks and kind regards
Aline
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group