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
"Greg Cocks said"
> In my data table <d_borehole> I have a field 'depth_unit' that is used
> 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",
> , etc
> I would like to restrict the foreign key for d_borehole.depth_unit to
> ly those values in r_unit.unit_id where r_unit.unit_length =3D
> e., so that only applicable 'length' units can be utilized for the
> 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
|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'.
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? :-)
In response to
pgsql-novice by date
|Next:||From: Sean Davis||Date: 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 Cocks||Date: 2007-12-29 00:01:09|
|Subject: Setting a FK to look at only selected rows in the 'look-up' table...|