From: Ross J. Reedstrom <reedstrm@rice.edu>
To: Patrick Welche <prlw1@newn.cam.ac.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: [HACKERS] RULES
Date: Wed, 21 Nov 2001 09:57:31 -0600
Message-ID: <20011121095731.C22603@rice.edu> (raw)
In-Reply-To: <20011121125837.E21102@quartz.newn.cam.ac.uk>
References: <001401c1721f$4425b8c0$0100a8c0@fabio3c1>
<20011121125837.E21102@quartz.newn.cam.ac.uk>
<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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: reedstrm@rice.edu, prlw1@newn.cam.ac.uk
Subject: Re: [HACKERS] RULES
In-Reply-To: <20011121095731.C22603@rice.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox