<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