Re: Foreign keys question (performance)

On 4 Dec 2011, at 11:19, Phoenix Kiula wrote:

> Hi.
>
> I have a foreign key as such:
>
>
> ALTER TABLE child_table
> ADD CONSTRAINT fk_child
> FOREIGN KEY (stringid) REFERENCES parent_table (stringid) MATCH FULL
> ON DELETE CASCADE ;
>
>
> Questions:
>
> 1. Is "MATCH FULL" adding any value here? If the foreign key is just
> on an "id" column, what purpose does it serve? Without it, the results
> would be the same? Does it affect performance or should I leave it be?
> (Note that the id is a alphanumeric value)

Nope, it is not. As I understand it, it only does something on multi-column foreign keys where parts of the key are NULL. To quote the documentation:

"There are three match types: MATCH FULL, MATCH PARTIAL, and MATCH SIMPLE, which is also the default. MATCH FULL will not allow one column of a multicolumn foreign key to be null unless all foreign key columns are null. MATCH SIMPLE allows some foreign key columns to be null while other parts of the foreign key are not null. MATCH PARTIAL is not yet implemented."

I can't say much on the impact on performance, but I'd expect that to be negligible in this case: With the MATCH FULL in place, it will need to check whether any of your columns are NULL, but that's only a single column in your case.

> 2. More importantly, in this case basically the child_table cannot
> have any keys that the parent_table doesn't have either. Will INSERTs
> and UPDATEs to the parent_table be slower? Or will the foreign key
> check happen only when INSERT or UPDATE happen to the child_table?

INSERTs in the parent table don't need to check for any reference from the child table, since they're new; there can't be a reference. UPDATEs and DELETEs do though, whether you let them CASCADE or not. If you don't, then the database raises a foreign key constraint violation. If you do, then it needs to modify the relevant rows in the child table.

Likewise, INSERTs and UPDATEs in the child table need to verify that - if their reference key changed - they're still referencing a valid row.

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.