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!