Re: Setting a FK to look at only selected rows in the 'look-up' table...

On Dec 28, 2007 7:01 PM, Greg Cocks <gcocks(at)stoller(dot)com> wrote:

> Hello,
>
> In my data table <d_borehole> I have a field 'depth_unit' that is used to
> record the measurement unit of various numeric depths down a borehole.
>
>
> I have a 'look-up' table <r_unit> that contains three fields:
>
> - 'unit_id' - PK, the abbreviation for the unit - example: "m"
>
> - 'description', fuller description of the unit - example: "metres"
>
> - 'unit_type', the nature (sic) of the unit - example: "length"
>
>
> The 'unit_type' field has a variety of values - "length", "ratio",
> "volume", etc
>
>
> I would like to restrict the foreign key for d_borehole.depth_unit to be
> only those values in r_unit.unit_id where r_unit.unit_length = 'length' (
> i.e., so that only applicable 'length' units can be utilized for the
> depths (feet, metres, inches, etc))
>

This cannot be done with foreign keys I don't think. I would store the
unit_id (an integer, not an abbreviation as above) as you have been doing
and then have your application code do the lookups for the appropriate
fields. Maintain the foreign key to be sure that your unit is in the "unit"
table, but your application would be responsible for making sure that length
units are used for lengths, width units for width, etc. You might want to
split out the unit_type into a separate lookup table to allow you to
add/modify unit_types easily and quickly.

Sean