references definition to multi-field primary key
От | Gary Stainburn |
---|---|
Тема | references definition to multi-field primary key |
Дата | |
Msg-id | 200208161615.57616.gary.stainburn@ringways.co.uk обсуждение исходный текст |
Ответы |
Re: references definition to multi-field primary key
|
Список | 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 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
В списке pgsql-sql по дате отправления: