Content-Type: text/plain;
  charset="us-ascii"
From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Organization: Ringways Garages Ltd
To: <pgsql-sql@postgresql.org>
Subject: references definition to multi-field primary key
Date: Fri, 16 Aug 2002 16:15:57 +0100
User-Agent: KMail/1.4.1
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-Id: <200208161615.57616.gary.stainburn@ringways.co.uk>

Hi all,

I've got some fuel pumps, each of which sell a number of fuel grades - one 
nozzle per grade, each nozzle connected to a specified tank containing that 
grade.

I can define the tanks, the pump numbers, and the pump grade/nozzle config 
using the tables below.

create table grades ( -- different types of fuel sold
  gid character,
  gdesc varchar(20),
  gprice float,
  primary key (gid)
);

create table tanks (  -- storage tanks
  tid int4 not null,
  tgrade character references grades(gid),
  primary key (tid)
);

create table pumps ( -- list of pumps
  pid int4 not null,
  primary key (pid)
);

create table pgrades ( -- list of nozzles/grades per pump
  pgpid int4 not null references pumps(pid),  
  pgnozzle int4 not null,
  pgtank int4 not null references tanks(tid),
  primary key (pgpid, pgseq)
);

My problem is that I want to be able to define a 'Pump Readings' table to show 
per pump/nozzle the opening and closing reading.  However, my problem is that 
I don't know how to define the references so that I can only create a 
preadings row for an existing pgrages entry.  Here's the table less the 
required references entry.

create table preadings ( -- daily reading per pump/nozzle
   prdate date not null,
   prpump int4 not null
   prnozzle int4, 
   propen integer,
   prclose integer,
   primary key (prdate, prpump, prseq)
);

I only want the insert to work if prpid matches pgpid and prnozzle matches 
pgnozzle.

-- 
Gary Stainburn
 
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000