Red Gate forums :: View topic - Only Populate One of Two 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

Only Populate One of Two Foreign Keys

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



Joined: 26 Jan 2010
Posts: 7

PostPosted: Wed Jan 27, 2010 12:16 am    Post subject: Only Populate One of Two Foreign Keys Reply with quote

I'm trying to generate data for something similar to:

CREATE TABLE Table1 (
Id int NOT NULL,
ForeignKey1 int NULL,
ForeignKey2 int NULL
)

The catch is that if ForeignKey1 is not null then ForeignKey2 needs to be null and vice-versa. Is this possible? I've embedded DLR language support into a custom generator. However, the fact that it's a foreign key would require me to access the db directly in the generator. Is there an easier way to do this?
Back to top
View user's profile Send private message
almasmith



Joined: 26 Jan 2010
Posts: 7

PostPosted: Wed Feb 03, 2010 7:02 pm    Post subject: Easy solution Reply with quote

Sometimes the solution is so easy we don't even think about it. No custom Iron Ruby generator needed at all. SDG has a section for running SQL scripts before or after generation, so this is what I did:

Set ForeignKey1 to allow nulls 50% of the time
Set ForeignKey2 to NOT allow nulls

Create a script to run AFTER generation that fixes it:

UPDATE Table1
SET ForeignKey2 = NULL
WHERE ForeignKey1 IS NOT NULL

That's it!
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