From: Sean Davis [mailto:sdavis2@mail.nih.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...
>> 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.