>Hello all,
>
>Assume the following RI rule:
>
>In table x, if column coll is not empty, the value in col1 must be the primary key in table y.
>
>IOW, I want to allow blank values in the field, but enforce RI on the foreign key when the field is not blank.
>
>I used the RI builder to create a relationship between the parent and children tables. It created triggers exceeding 80 lines each (is this a performance problem?). The triggers it created will not let me leave column col1 blank in this example.
>
>I see a few options to overcome this situation:
>
>1) Add a record in the referenced (or lookup) table with an empty primary key. This is easy, it works, but records with empty keys are bad things.
>
>2) Modify the trigger the builder wrote to overlook blank values. Of course, the modification will be lost the next time I run RI builder (is this a true statement?)
>
>3) Write triggers manually.
>
>4) Don't use triggers. Enforce somewhere else (perhaps in a business object)
>
>I know there are several questions wrapped into this. I value everyone's input here.
>
>Thanks a bunch,

The RI builder sometimes fails to generate correct RI code. I find that when this happens, I just rerun the RI builder and it works fine. It correctly merges the RI code with my existing stored procedure (I have only one, for generating keys). I think you can write your own stored procedure just for this one thing, and allow the RI builder to build the rest of your RI code. You might do without a persistent relation between tableX and tableY, or put one in but choose "ignore" for all its triggers.

I don't think the generated RI code is slow, but perhaps someone has written faster code without sacrificing robustness. I think that certain operations, such as cascades, are bound to take time. The RI builder generates separate procedures for each table, which can add up to an overflow or something if you have hundreds of tables. Booth and Sawyer have a set of RI code in their Effective Techniques book. It receives the names of the tables as a parameter and runs the same code for each.