Scott Marlowe wrote:
As a secondary question, is there any way I could have answered this
myself, using analyze, the system catalogs, etc? ANALYZE DELETE
doesn't seem to show the FK checking that must go on behind the
scenes.
You could have coded up an example to see if it worked I guess.
Here's a short example:
create table a (i int, j int, info text, primary key (i,j));
create table b (o int, p int, moreinfo text, foreign key (o,p)
references a);
insert into a values (1,2,'abc');
insert into b values (1,2,'def');
INSERT 0 1
insert into b values (1,3,'def');
ERROR: insert or update on table "b" violates foreign key
constraint "b_o_fkey"
DETAIL: Key (o,p)=(1,3) is not present in table "a".
delete from a;
ERROR: update or delete on table "a" violates foreign key constraint
"b_o_fkey" on table "b"
DETAIL: Key (i,j)=(1,2) is still referenced from table "b".
But this doesn't really match my question - I wanted to know whether
checking an FK on =one= column would use a composite key on =several=
columns. Modifying your example:
create table a (i int PRIMARY KEY, j int, info text);
create table b (o int REFERENCES A, p int, moreinfo text, PRIMARY
KEY (O,P));
insert into a values (1,2,'abc');
insert into b values (1,2,'def');
delete from a where i = 1;
Here, the FK is a simple one, and the referential integrity machinery
simply needs to check whether there is a row in table B with O=1. My
question is whether it will use the composite PK index.
I guess a generalization of my question is whether the FK-checking
machinery simply does a SELECT against the referencing column. That
is, in this example, if the following effectively happens:
SELECT * FROM B WHERE O = 1;
then PG will use whatever index might make the query faster. Is this
in fact the case, that I should think of the FK machinery as simply
doing the appropriate SELECT?
Thanks.
- John D. Burger
MITRE
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
