MySQL Bugs: #3325: Index mandatory for foreign key in InnoDb

Bug #3325	Index mandatory for foreign key in InnoDb
Submitted:	29 Mar 2004 7:40	Modified:	1 Jun 2004 11:08
Reporter:	fabiola Porta	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	4.1	OS:	HP/UX (HP-UX 11.11)
Assigned to:	Marko Mäkelä	CPU Architecture:	Any

Description:
In InnoDb the index on foreign key  must be declared explicitly. It is different from ORACLE behaviour.

How to repeat:
Normal behaviour

---------

Hi!

I am changing this bug report to a 'Feature request'. Also in Oracle, it good to create an index on the foreign key, because otherwise your deletes in the parent table need to scan the child table to determine if there is a child row.

Regards,

Heikki

I also think that the index on the foreign key should not be strictly necessary. Or it should only be necessary when it is something like an "on delete cascade" constraint.

Hi!

Marko has written a patch to 4.1. I hope it will be included in 4.1.2.

The patch automatically creates an index on the FOREIGN KEY. Note that it does not create an index on the REFERENCED KEY, but that is not that much of a problem, since the referenced key is usually the PRIMARY KEY of the referenced table, and has an index on it anyway.

Regards,

Heikki

Fix is likely to appear in 4.1.2.

Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 
http://www.mysql.com/downloads/

Additional info:

A fix for this bug is included in the 4.1.2 release. See http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html:
"InnoDB needs indexes on foreign keys and referenced keys, so that foreign key checks can be fast and not require a table scan. Starting with MySQL 4.1.2, these indexes are created automatically. In older versions of MySQL/InnoDB, the indexes have to be created explicitly, or the creation of foreign key constraints will fail."