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

["Greg Cocks" <gcocks(at)stoller(dot)com>]

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))


I tried:

 - making the FK without the added constraint in pgAdmin, copying the SQL and adding a WHERE statement after the REFERENCE - no luck...

 - making a VIEW where the r_unit data is parsed in the desired way and trying to use this in the FK definition - no luck, it 'likes' only tables...

 - Googling!    :-)

*Suggestions?*

And yes, per this list a 'newbie'...   :-)

Thanks in advance...


----------
Regards,
GREG COCKS
GIS Analyst V
Gcocks |at| stoller.com
S. M. Stoller Corp
105 Technology Drive, Suite 190
Broomfield, CO 80021
www.stoller.com
303-546-4300
303-443-1408 fax
303-546-4422 direct
303-828-7576 cell