From: | "Greg Cocks" <gcocks(at)stoller(dot)com> |
---|---|
To: | "PostgreSQL List - Novice" <pgsql-novice(at)postgresql(dot)org> |
Subject: | Setting a FK to look at only selected rows in the 'look-up' table... |
Date: | 2007-12-29 00:01:09 |
Message-ID: | 66F6CF82BF58CE4DB4285BE816B297E83BA7CC@tribble.SMStoller.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Cocks | 2007-12-29 01:50:23 | Re: Setting a FK to look at only selected rows in the 'look-up' table... |
Previous Message | G. J. Walsh | 2007-12-27 00:41:26 | phppgadmin for postgresql 8.3 beta 4 |