Bug #12658 Want to drop index on fk.
Submitted: 18 Aug 2005 21:14 Modified: 13 May 2010 16:04
Reporter: Andre Timmer Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:5 OS:Solaris (Solaris)
Assigned to: Assigned Account CPU Architecture:Any

[18 Aug 2005 21:14] Andre Timmer
Description:
When FK is created also an index is created.
This is not always desirable, in those cases i want to drop the index afterwards.

This doesn't work / is not allowed.

How to repeat:
.

Suggested fix:
Make it possible.
[19 Aug 2005 8:30] Heikki Tuuri
Hi!

The current foreign key implementation in InnoDB always requires an index to exist on the foreign key and the referenced key.

Why do you want not to have an index?

Regards,

Heikki
[19 Aug 2005 8:58] Andre Timmer
Because inserting and deleting is unneccessairy expensive.
We have (several) tables with 10+ columns witch do have as an average 3 fk's that need a index. Bug as an average 5 fk's that don't need one! But we need the fk's for integrety!
[19 Aug 2005 8:59] Andre Timmer
Millions of rows are inserted during initial load so performance does matter.
[22 Aug 2005 15:33] Heikki Tuuri
Andrew,

ok, I see. If there are only, say, 5 rows in the referenced table, then it wastes disk space to have an index in the referencing table.

As you say, using an enum is a workaround if the possible values in the referenced table are known beforehand.

Regards,

Heikki
[22 Aug 2005 15:51] Andre Timmer
Enum is a workaround in some cases, not all:
- for example when the domain of the column has many values
- if the column has few values then, for a reliable database, it has to run in strict mode

The advantage of a FK is that for InnoDb tables it works in all cases.
So when loading hughes amounts of data in referentially secured database also FK's without indexes would be really great! It really would help / be of practical value.