Skip site navigation (1) Skip section navigation (2)

Re: composite primary key to foreign key

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: Ryan Vaughan <ryanv(at)mminternet(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: composite primary key to foreign key
Date: 2004-03-13 20:20:47
Message-ID: 20040313121850.T21437@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-novice
On Fri, 12 Mar 2004, Ryan Vaughan wrote:

> I have a table with 4 columns, 3 of which make up a composite primary key.
> None of these columns are unique, but the combination of the the three
> primary keys will be unique:
>
> CREATE TABLE Table1 (
>    col1 integer,
>    col2 varchar(20),
>    col3 varchar(20),
>    col4 varchar(20),
>    CONSTRAINT PRIMARY KEY (col1, col3, col4)
> );
>
> In another table I have 3 columns:
>
> CREATE TABLE Table2 (
>    field1 integer,
>    field2 integer,
>    field3 integer,
> );
>
> I'd like to have field2 in table2 be a foreign key of col1 in table1.  Since
> col1 is not unique, is this possible or do I have to either bring in
> extraneous data from table1...col1, col2, and col3, or create some unique
> counter in table1 to use as reference?  All I care about in field2 of table2

Pretty much one of these is what is required to use foreign keys.

> is that it matches something in col1 in table1, I don't want the other data
> in table1.  I've searched archives but maybe I'm not looking for the right
> thing.  Thanks.

However, are you actually looking for a true foreign key constraint? Many
times people don't care about the reverse prevention of update/delete to
Table1 being prevented.  If that's the case you're in, a simpler trigger
in plpgsql may be closer to what you want.

In response to

pgsql-novice by date

Next:From: Tom LaneDate: 2004-03-13 23:26:26
Subject: Re: PostgreSQL 7.2.1 on OS X -- psql: FATAL 1: user "postgres" does not exist
Previous:From: Eric WilkeDate: 2004-03-13 09:10:55
Subject: PostgreSQL 7.2.1 on OS X -- psql: FATAL 1: user "postgres" does not exist

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group