"John Burger" <john@xxxxxxxxx> writes: > 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. It does > That is, in this > example, if the following effectively happens: > > SELECT * FROM B WHERE O = 1; Actually the query is (effectively, assuming your equality operators are named "=" and the columns match in type) SELECT 1 FROM ONLY B x WHERE col1=? AND col2=? ... FOR SHARE OF x Since it has to take a lock on the record found to ensure it doesn't disappear before your transaction finishes. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support! ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
