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
notnull, 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
