Message-ID: <3E00A678.5070605@klaster.net>
Date: Wed, 18 Dec 2002 17:46:48 +0100
From: Tomasz Myrta <jasiek@klaster.net>
User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; en-US;
	rv:1.2b) Gecko/20021016
MIME-Version: 1.0
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: references table(multiple columns go here)
References: <200212181625.11316.gary.stainburn@ringways.co.uk>
In-Reply-To: <200212181625.11316.gary.stainburn@ringways.co.uk>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Hello again

Gary Stainburn wrote:

> Hi folks,
>
> how do I define a referene from 2 columns in 1 table to 2 columns in 
> another.
>
> I have:
>
> create table ranks (
> rid     	int4 default nextval('ranks_rid_seq'::text) unique not null,
> rdid		character references depts(did), -- department
> rrank		int4 not null,		-- departmental rank
> rdesc		character varying(40)	-- Rank Description
> );
>
>
>
>
> I would now like to define the following table so that inserts can 
> only happen
> if jdid matches rdid and jrank matches rrank.
>
> create table jobtypes (
> jid     	int4 default nextval('jobs_jid_seq'::text) unique not null,
> jdid		character references ranks(rdid),	-- This joint reference
> jrank		int4 not null references ranks(rrank),	-- needs sorting
> jdesc		character varying(40)	-- job description

!!!here!!! ,CONSTRAINT c2 FOREIGN KEY(jid,jdid) REFERENCES ranks (rid,rdid)

> );

that's all
Tomasz Myrta