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,
Steve Gibson