Thread: 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
numericdepths 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

--------------------------------

-----Original Message-----
From: Jeff Waugh [mailto:jwaugh@griddlecat.com]
Sent: Friday, December 28, 2007 6:24 PM
To: Greg Cocks
Subject: Re: [NOVICE] Setting a FK to look at only selected rows in the
'look-up' table...

"Greg Cocks said"
>
> Hello,
>
> In my data table <d_borehole> I have a field 'depth_unit' that is used
to r=
> ecord 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 on=
> ly those values in r_unit.unit_id where r_unit.unit_length =3D
'length' (i.=
> e., so that only applicable 'length' units can be utilized for the
depths (=
> feet, metres, inches, etc))
>
>
|
|Instead of storing the depth_unit in d_borehole, store depth_unit_id.
|
|Then it would be something like:
|
|alter table d_borehole add constraint unit_type_fk
|(depth_unit_id) references r_unit (unit_id);
|
|unit_id will need to be the primary key (or at least unique) in r_unit.
|
|That is pretty much the standard way to use lookup tables. Don't store
the
|lookup value anywhere except the lookup table. Anywhere else you want
|that lookup value, store the primary key from the lookup table, then
|when you need the text 'look it up'.

|HTH.


Jeff,

Thanks for the reply...

I think I am missing something - sorry...

It seems that is the way I have it now - i.e., 'depth_unit' == 'unit_id'
(sic), where the later is the PK in the look up table <r_unit>

That is, the names are just different - maybe they should not be?

If I normalized (sic?) it by using, say, an integer for the unit_id I
both places it seems to me that there would still be the same issue of
how to parse the values 'available' through the FK from <r_unit> to be
*only* be those where the unit_type = 'length' in <r_unit> i.e.:

SELECT r_unit.unit_id FROM r_unit WHERE r_unit.unit_type)='length';

Note that another data table might, say, only be "allowed" to use
unit_type = 'velocity'

Aside - I like using the abbreviated and yet unique values for the unit
identifier - ft, m, ppm, ppb, etc - as the 'raw' data table seems that
much more readable - and I don't appear to be causing any issues - do I
need to be corrected on this approach?  :-)

Cheers:
GREG...

"Sean Davis" <sdavis2@mail.nih.gov> writes:
> On Dec 28, 2007 7:01 PM, Greg Cocks <gcocks@stoller.com> wrote:
>> 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.

Maybe I missed something, but couldn't a two-column foreign key
constraint on (value, unit) be made to serve the purpose?

            regards, tom lane