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

Sean, Tom, Others...

Two column FK - perfect! I have used them elsewhere, not sure why I did
not think of them in this instance!

"Thanks list..." :-)

Cheers:

GREG COCKS

gcocks(at)stoller(dot)com <mailto:gcocks(at)stoller(dot)com>

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

________________________________

From: Sean Davis [mailto:sdavis2(at)mail(dot)nih(dot)gov]
Sent: Friday, December 28, 2007 8:29 PM
To: Tom Lane
Cc: Greg Cocks; PostgreSQL List - Novice
Subject: Re: [NOVICE] Setting a FK to look at only selected rows in the
'look-up' table...

On Dec 28, 2007 10:11 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

"Sean Davis" <sdavis2(at)mail(dot)nih(dot)gov> writes:
> On Dec 28, 2007 7:01 PM, Greg Cocks <gcocks(at)stoller(dot)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?

Yep. If he modifies his schema somewhat to include the extra column in
the first table.

Sean