MySQL Bugs: #17906: Allow FOREIGN KEYs without an index

Bug #17906	Allow FOREIGN KEYs without an index
Submitted:	3 Mar 2006 20:19	Modified:	16 Mar 2006 13:03
Reporter:	Andre Timmer	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	5.0.18	OS:	Any (all)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Dropping index fails.

How to repeat:
create table test1 (id integer) engine = innodb;
alter  table test1 add primary key (id);

create table test2 (id integer, col2 integer, col3 integer) engine = innodb; 
alter  table test2 add foreign key fk_index (col2) references test1 (id);

alter table test2 drop index fk_index;
ERROR 1025 (HY000): Error on rename of './bbr/#sql-7425_b' to './bbr/test2' (errno: 150)

Suggested fix:
Why does some one want to have a foreign key, but no index?

So many small reference tables can be used on a hugh tables.
And enums are not an option but a consistent dataset is a must.

---------------

What really would be great if not creating an index would be supported in foreign key syntacs.

Thank you for a problem report. Everything works just as you described.

I agree that there are cases when unindexed foreign keys should be allowed. That is why, say, Oracle creates them as such by default. But I think this is not really a bug, but a documented behaviour. Read the manual (http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html):

"In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist."

So, it is a feture request. At least, for proper error message in such cases, as well as for the real feature (unindexed foreign keys). I am not sure the later feature will be added, though.

Duplicate of http://bugs.mysql.com/bug.php?id=12658