On Nov 29, 2007 10:51 AM, John Burger <john@xxxxxxxxx> wrote: > Hi - > > I know that the foreign key machinery will use an index on the > referring column if one exists. My question is whether it will use a > composite index? For instance: > > create table allLemmaSenseMap ( > wordID integer references allLemmas, > senseID integer references allSenses, > primary key (wordID, senseID) > ); > > If I delete something from allLemmas, will the FK check use the PK > index above? (I know I should at least have an index on senseID as > well, because of the other foreign key.) Yes. It will > 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". ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
