Received: from ece.rice.edu (ece.rice.edu [128.42.4.34])
	by postgresql.org (8.11.3/8.11.4) with ESMTP id fALFvRm44196
	for <pgsql-sql@postgresql.org>; Wed, 21 Nov 2001 10:57:28 -0500 (EST)
	(envelope-from reedstrm@rice.edu)
Received: from localhost (localhost [127.0.0.1])
	by ece.rice.edu (Postfix) with ESMTP
	id 2041268A5A; Wed, 21 Nov 2001 09:57:32 -0600 (CST)
Received: from wallace.ece.rice.edu (wallace.ece.rice.edu [128.42.12.154])
	by ece.rice.edu (Postfix) with ESMTP
	id 5AAE668A1D; Wed, 21 Nov 2001 09:57:31 -0600 (CST)
Received: from reedstrm by wallace.ece.rice.edu with local (Exim 3.22 #1
	(Debian)) id 166Zkh-0005vI-00; Wed, 21 Nov 2001 09:57:31 -0600
Date: Wed, 21 Nov 2001 09:57:31 -0600
From: "Ross J. Reedstrom" <reedstrm@rice.edu>
To: Patrick Welche <prlw1@newn.cam.ac.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: [HACKERS] RULES
Message-ID: <20011121095731.C22603@rice.edu>
Mail-Followup-To: "Ross J. Reedstrom" <reedstrm@ece.rice.edu>,
	Patrick Welche <prlw1@newn.cam.ac.uk>, pgsql-sql@postgresql.org
References: <001401c1721f$4425b8c0$0100a8c0@fabio3c1>
	<20011121125837.E21102@quartz.newn.cam.ac.uk>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20011121125837.E21102@quartz.newn.cam.ac.uk>
User-Agent: Mutt/1.3.18i
X-Virus-Scanned: by AMaViS snapshot-20010714
X-Archive-Number: 200111/303
X-Sequence-Number: 5685

<note that this is not really HACKERs type material, so I moved the
response to the SQL list: I'm CCing Patrick directly, since I don't 
know if he reads that list>

On Wed, Nov 21, 2001 at 12:58:37PM +0000, Patrick Welche wrote:
> 
> create table a (
>   id    integer primary key
> );
> 
> create table b (
>   a_id  integer references a(id) match full
> );
> 
> select * from pg_trigger where tgname ~* '^RI_';
> 
> Gives me 3 rows. They all contain the same tgargs. Is it therefore
> sufficient to select distinct tgnargs,tgargs if I just want to be able to
> recreate the "references... match full" part of the create table statement?
> 
> It seems that the rows differ in
> 
> tgtype	tgrelid		tgconstrrelid	tgfoid
>  9		table a		table b 		RI_FKey_noaction_del
> 17		table a		table b			RI_FKey_noaction_upd
> 21		table b		table a			RI_FKey_check_ins
> 
> 9=row,delete, 17=row,update, 21=row,insert,update ?
> 
> Why are the first 2 constraints there? It seems to be the last one which
> says "If I insert,update table b, check it is a valid entry with table a"
> 
> Is that right?

As far as it goes. Realize that a primary key <-> foreign key relationship
is two way: it constrains the parent table as well as the child.

Consider what happens if you have something like this:

test=# select * from a;
 id 
----
  1
  2
  3
  4
(4 rows)

test=# select * from b;
 a_id 
------
    1
    1
    3
    3
    2
    1
    3
(7 rows)

test=# 

So, what happens if you do:

test=# delete from a where id=4;
DELETE 1
test=# delete from a where id=3;
ERROR:  <unnamed> referential integrity violation - key in a still referenced from b
test=# update a set id=4 where id=3;
ERROR:  <unnamed> referential integrity violation - key in a still referenced from b

Since the key is still in use in b, it can't be deleted or modified in a.
Note that if the key had been setup as a CASCADE, then modifying (or deleting)
from a would effect b as well, as so:

drop table b;

create table b ( a_id  integer references a(id) match full ON UPDATE cascade);

<fill with some data>

test=# select * from b;
 a_id 
------
    3
    3
    1
    1
    2
    3
(6 rows)

test=# update a set id=4 where id=3;
UPDATE 1
test=# select * from b;
 a_id 
------
    1
    1
    2
    4
    4
    4
(6 rows)

Pretty cool, huh?

Ross
-- 
Ross Reedstrom, Ph.D.                                 reedstrm@rice.edu
Executive Director                                  phone: 713-348-6166
Gulf Coast Consortium for Bioinformatics              fax: 713-348-6182
Rice University MS-39
Houston, TX 77005