Only Populate One of Two Foreign Keys

The simplest way to populate your database with test data.

Moderators: David Atkinson, Anu Deshpande, Lionel

Only Populate One of Two Foreign Keys

Postby almasmith » Wed Jan 27, 2010 12:16 am

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?
almasmith
 
Posts: 7
Joined: Tue Jan 26, 2010 10:55 pm

Easy solution

Postby almasmith » Wed Feb 03, 2010 7:02 pm

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!
almasmith
 
Posts: 7
Joined: Tue Jan 26, 2010 10:55 pm


Return to SQL Data Generator 1

Who is online

Users browsing this forum: No registered users and 0 guests