Re: Will PG use composite index to enforce foreign keys?

"John Burger" <john(at)mitre(dot)org> 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!