469,110 Members | 1,986 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,110 developers. It's quick & easy.

Unindexed foreign keys and full table scans

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
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
Jul 19 '05 #1
0 2834

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

10 posts views Thread by Bodza Bodza | last post: by
9 posts views Thread by Jax | last post: by
6 posts views Thread by Victor Spång Arthursson | last post: by
reply views Thread by Carlos Ibarra | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.