Re: [HACKERS] RULES

<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(at)rice(dot)edu
Executive Director phone: 713-348-6166
Gulf Coast Consortium for Bioinformatics fax: 713-348-6182
Rice University MS-39
Houston, TX 77005