MySQL Bugs: #12658: Want to drop index on fk.

Bug #12658	Want to drop index on fk.
Submitted:	18 Aug 2005 23:14	Modified:	22 Aug 2005 17:51
Reporter:	Andre Timmer
Status:	Open
Category:	Server: InnoDB	Severity:	S4 (Feature request)
Version:	5	OS:	Sun Solaris (Solaris)
Assigned to:	Heikki Tuuri	Target Version:
Triage:	D5 (Feature request)

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.

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

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!

Millions of rows are inserted during initial load so performance does matter.

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

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.