Indexing FOREIGN KEY
Posted by: Hugh Messenger
Date: May 19, 2006 11:51AM

Is there any general rule of thumb concerning foreign key indexing? For instance, here's part of my schema where I'm defining staff members. I have separate tables for contact 'numbers' (phone, email, url, etc), and physical 'address'. The 'numbers' is required, 'address' isn't.

CREATE TABLE staff (
staff_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
[... blah blah ...]
numbers_id SMALLINT UNSIGNED NOT NULL,
address_id SMALLINT UNSIGNED DEFAULT NULL,
[... blah blah ...]
PRIMARY KEY (staff_id),
KEY idx_fk_numbers_id (numbers_id),
KEY idx_fk_address_id (address_id),
[... blah blah ...]
CONSTRAINT fk_staff_numbers FOREIGN KEY (numbers_id) REFERENCES numbers(numbers_id) ON DELETE RESTRICT ON UPDATE CASCADE
CONSTRAINT fk_staff_address FOREIGN KEY (address_id) REFERENCES address(address_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

In the above, I've created an index for each of the foreign keys. I'm just not sure under what circumstances such indexes are required, recomended or not required.

I have some more complex examples I'd like to understand, but hopefully I can work those out myself if I can grok the basic rules about key indexing.

Even an RTFM response would be gratefuly received, as long as you include a link to which FM to R. :)

TIA,

-- hugh

Options: ReplyQuote


Subject
Written By
Posted
Indexing FOREIGN KEY
May 19, 2006 11:51AM
May 19, 2006 02:37PM
May 19, 2006 04:59PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.