MySQL Bugs: #10082: can't create foreign key constraint without index

Bug #10082	can't create foreign key constraint without index
Submitted:	22 Apr 2005 0:59	Modified:	22 Apr 2005 1:11
Reporter:	John Spencer	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	4.0.24	OS:	Linux (linux)
Assigned to:		CPU Architecture:	Any

View
Add Comment
Files
Developer
Edit Submission
View Progress Log
Contributions

Description:
errno 150 when trying to add foreign key constraint on a column which is not indexed.

How to repeat:
create 2 tables with one refering to the other but not indexed. Then alter table to add the foreign key.  This includes adding the foreign key with an index name or without the index name

Suggested fix:
should be able to add fk without index, or when index name is specified, then add the index automatically

Hi!

MySQL-4.1 does create an index automatically on the referencing column(s).

Creating an index automatically on the referenced columns is more difficult. I think many database brands only allow the primary key to be the referenced thing.

Regards,

Heikki