Postgres Pro
Downloads
Home   >   mailing lists

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

From Greg Cocks
Subject Re: Setting a FK to look at only selected rows in the 'look-up' table...
Date
Msg-id 66F6CF82BF58CE4DB4285BE816B297E83BA9B6@tribble.SMStoller.com
Whole thread Raw
In response to Re: Setting a FK to look at only selected rows in the 'look-up' table...  ("Sean Davis" <sdavis2@mail.nih.gov>)
List pgsql-novice
Tree view

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…”     J

 

 

Cheers:

GREG COCKS

gcocks@stoller.com

 

 

 

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

 

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

 

 

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

 

pgsql-novice by date:

Previous
From: Tom Lane
Date:
Subject: Re: Combined PITR/pg_dump backups?
Next
From: "sdger erger"
Date:
Subject: Command line output - How to output values only?