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

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

From: "Sean Davis" <sdavis2(at)mail(dot)nih(dot)gov>
To: "Greg Cocks" <gcocks(at)stoller(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 02:41:43
Message-ID: 264855a00712281841w1ad36467w89764799fedf43b5@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
On Dec 28, 2007 7:01 PM, Greg Cocks <gcocks(at)stoller(dot)com> wrote:

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

This cannot be done with foreign keys I don't think.  I would store the
unit_id (an integer, not an abbreviation as above) as you have been doing
and then have your application code do the lookups for the appropriate
fields.  Maintain the foreign key to be sure that your unit is in the "unit"
table, but your application would be responsible for making sure that length
units are used for lengths, width units for width, etc.  You might want to
split out the unit_type into a separate lookup table to allow you to
add/modify unit_types easily and quickly.

Sean

In response to

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2007-12-29 03:11:42
Subject: Re: Setting a FK to look at only selected rows in the 'look-up' table...
Previous:From: Greg CocksDate: 2007-12-29 01:50:23
Subject: Re: 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