pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Albert REINER <areiner@tph.tuwien.ac.at>
To: PostgreSQL-SQL <pgsql-sql@postgreSQL.org>
Subject: Ensuring primary key is referenced at least once upon commit
Date: Wed, 3 Jan 2001 00:26:50 +0100
Message-ID: <20010103002650.B1265@frithjof> (raw)

Saluton,

suppose we have PostgreSQL 7.0.2 and two tables, a and b, like:

a: id serial primary key
   ...

b: a int4 not null references a(id)
   ...

In general there will be several rows in b with the same b.a, but how
can I make sure there is AT LEAST ONE row referencing a given id from
table a?

It seems obvious to me that this can only be meaningful in a
transaction, like:

begin
insert into a ...;
insert into b (a,...) values (curr_val(a_id_seq),...);
commit

And the check can only be made before committing.

My first idea (which was not very good) was to add a table constraint
on table a similar to `... foreign key (a) references b(a) initially
deferred', because only a foreign key seems to allow checks to be
deferred, and I cannot tell from the docs whether a foreign key is
actually incompatible with a primary key declaration on the same
field. - Of course this does not work, as there is no table b by the
time a is created, or vice versa.

Looking at the documentation for CREATE TRIGGER, I do not see how to
get it to fire only just before commit - I would need a syntax like
create trigger ... before commit insert on a execute ..., which is not
what is there.

Rules - another one of those exotic things I never thought I might
actually need - do not seem to provide a solution, either.

As long as I know that nobody will mess around with the database
directly but only with scripts I provide, I can easily provide the
necessary checks etc., so this may be not so much of an issue really.
Still, it would be good to know that this works, and I am pretty sure
that this must be possible, but I seem to be looking in the wrong
direction. Any help would be appreciated.

Thanks in advance,

Albert.


-- 

--------------------------------------------------------------------------
Albert Reiner                                   <areiner@tph.tuwien.ac.at>
Deutsch       *       English       *       Esperanto       *       Latine
--------------------------------------------------------------------------



Message-ID: <20010103002650.B1265@frithjof>
Permalink:  ../20010103002650.B1265@frithjof/
Also on:    postgresql.org/message-id/20010103002650.B1265@frithjof

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: areiner@tph.tuwien.ac.at
  Subject: Re: Ensuring primary key is referenced at least once upon commit
  In-Reply-To: <20010103002650.B1265@frithjof>
  References: <20010103002650.B1265@frithjof>

* 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