Re: [HACKERS] RULES - Mailing list pgsql-sql

<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