references definition to multi-field primary key

pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed

references definition to multi-field primary key
2+ messages / 2 participants
[nested] [flat]

* references definition to multi-field primary key
@ 2002-08-16 15:15  Gary Stainburn <gary.stainburn@ringways.co.uk>
  0 siblings, 1 reply; 2+ messages in thread

From: Gary Stainburn @ 2002-08-16 15:15 UTC (permalink / raw)
  To: pgsql-sql

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     

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: references definition to multi-field primary key
@ 2002-08-16 15:59  Bruno Wolff III <bruno@wolff.to>
  parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
  0 siblings, 0 replies; 2+ messages in thread

From: Bruno Wolff III @ 2002-08-16 15:59 UTC (permalink / raw)
  To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql

On Fri, Aug 16, 2002 at 16:15:57 +0100,
  Gary Stainburn <gary.stainburn@ringways.co.uk> wrote:
> 
> I only want the insert to work if prpid matches pgpid and prnozzle matches 
> pgnozzle.

There are several typos in your sample that make it hard to know for
sure what you want to do. However I suspect you just want to add a
foreign key clause to your table definition. These can refer to more
than one column (unlike references). My memory is that you need a matching
unique index defined for the table you are referencing.

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2002-08-16 15:59 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-08-16 15:15 references definition to multi-field primary key Gary Stainburn <gary.stainburn@ringways.co.uk>
2002-08-16 15:59 ` Bruno Wolff III <bruno@wolff.to>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox