Received: from frithjof.reiner (a117.dialin.tuwien.ac.at [192.35.240.127]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f03H1C700630 for ; Wed, 3 Jan 2001 12:01:16 -0500 (EST) (envelope-from areiner@tph.tuwien.ac.at) Received: (qmail 1462 invoked by uid 500); 2 Jan 2001 23:26:50 -0000 From: "Albert REINER" Date: Wed, 3 Jan 2001 00:26:50 +0100 To: PostgreSQL-SQL Subject: Ensuring primary key is referenced at least once upon commit Message-ID: <20010103002650.B1265@frithjof> Mail-Followup-To: PostgreSQL-SQL Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.2.5i X-Archive-Number: 200101/34 X-Sequence-Number: 695 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 Deutsch * English * Esperanto * Latine --------------------------------------------------------------------------