Generate composite key from 2 Foreign keys

Aline

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

Lionel

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

Aline

This patch works wonderfully.

Thank you.

Aline

Aline

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

Is there another patch?

Thanks and kind regards
Aline