Thread: Column References

Column References

From
"Epps, Aaron M."
Date:
    I've got a question about references, is it possible to specify criteria for the Reference.  For example, Table 1 has Field1 That References Field1 in Table2, However I only want Field 1 in Table 1 to reference the Records in Table2 that have the Valid Field set to TRUE.  In other words, I don't want the user to be able to enter invalid records from table 2 into field 1 in table 1.  Is there a different way of accomplishing the same thing?
 

Re: Column References

From
Bruno Wolff III
Date:
On Fri, Oct 31, 2003 at 13:44:40 -0600,
  "Epps, Aaron M." <Epps.Aaron@mayo.edu> wrote:
>     I've got a question about references, is it possible to specify criteria for the Reference.  For example, Table 1
hasField1 That References Field1 in Table2, However I only want Field 1 in Table 1 to reference the Records in Table2
thathave the Valid Field set to TRUE.  In other words, I don't want the user to be able to enter invalid records from
table2 into field 1 in table 1.  Is there a different way of accomplishing the same thing? 

The way I have done this is to use redundant data in table1. For example in
your case you might include a Valid field in table 1 with a constraint
forcing it to always be true. Then you can use a two column foreign key
reference from table 1 to table 2 that will enforce your constraint.
(You will also need to make a unique index on the two column combination
in table2.)