Unindexed foreign keys and full table scans
Date: 3 Jun 2004 08:05:28 -0700
Message-ID: <89af12dd.0406030705.6cac8b37_at_posting.google.com>
In various places I have seen mentioned the importance of indexing foreign keys to avoid table locks on the child table on parent update/delete and full table scans when the constraint has action ON DELETE CASCADE. For example, see
However, if I have no index on the FKs, the only mention of full table
scans on the child table is when the constraint specifies ON DELETE
CASCADE. But what happens if the RI constraint is set to the default
NO
ACTION, meaning that it inhibits any operation that would create
orphans? If I delete a parent table row, and in the absence of an
index, doesn't it then have to perform a full table scan on the child
table to make sure there are no references to the parent row?
An EXPLAIN of such a query doesn't seem to show any such scan.
If it doesn't perform a scan, how can it verify that there are no references and it's safe to delete the parent row? Is there some kind of internal reference count per parent row that is updated when any child row is inserted or deleted?
- Carlos A. Ibarra