Only Populate One of Two Foreign Keys

almasmith

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

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!