Skip site navigation (1) Skip section navigation (2)

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

From: "Greg Cocks" <gcocks(at)stoller(dot)com>
To: "Jeff Waugh" <jwaugh(at)griddlecat(dot)com>
Cc: "PostgreSQL List - Novice" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Setting a FK to look at only selected rows in the 'look-up' table...
Date: 2007-12-29 01:50:23
Message-ID: 66F6CF82BF58CE4DB4285BE816B297E83BA7CD@tribble.SMStoller.com (view raw or flat)
Thread:
Lists: pgsql-novice
--------------------------------
 
-----Original Message-----
From: Jeff Waugh [mailto:jwaugh(at)griddlecat(dot)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...

In response to

pgsql-novice by date

Next:From: Sean DavisDate: 2007-12-29 02:41:43
Subject: Re: Setting a FK to look at only selected rows in the 'look-up' table...
Previous:From: Greg CocksDate: 2007-12-29 00:01:09
Subject: Setting a FK to look at only selected rows in the 'look-up' table...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group