Competition: What’s your favorite Redgate tool? Enter now.

Only Populate One of Two Foreign Keys

almasmithalmasmith Posts: 10
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?

Comments

  • 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!
Sign In or Register to comment.