From: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: pgsql-sql@postgresql.org
Subject: references definition to multi-field primary key
Date: Fri, 16 Aug 2002 16:15:57 +0100
Message-ID: <200208161615.57616.gary.stainburn@ringways.co.uk> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gary.stainburn@ringways.co.uk
Subject: Re: references definition to multi-field primary key
In-Reply-To: <200208161615.57616.gary.stainburn@ringways.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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