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

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