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@sss.pgh.pa.us> wrote:

> "Sean Davis" <sdavis2@mail.nih.gov> writes:
> > On Dec 28, 2007 7:01 PM, Greg Cocks <gcocks@stoller.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 Received on Fri Dec 28 22:31:08 2007