MySQL Bugs: #48728: Can't create unique index on foreign key

Bug #48728	Can't create unique index on foreign key
Submitted:	12 Nov 2009 13:49	Modified:	21 Apr 2010 15:30
Reporter:	Petr Burian	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S3 (Non-critical)
Version:	5.1.18	OS:	Linux (Kubuntu 9.10)
Assigned to:	Alfredo Kojima	CPU Architecture:	Any
Tags:	foreign key, unique index

Description:
I am afraid there is no way how to create a unique on INDEX which is also used as foreign key. I need this behaviour to simulate 1:1 relationship, which is also not fully supported in Workbench (see bug #43920).

I found only one solution - create duplicate index.

How to repeat:
Open attached mwb. There is simple situation - standard 1:1 relation. I need to have "table2_id" unique. If I open Indexes editor and try to change "Foreign to Unique", Workbench always revert my changes back.

In export I get:
INDEX `fk_table1_table2` (`table2_id` ASC) ,

but I want:
UNIQUE INDEX `fk_table1_table2` (`table2_id` ASC) ,

MWB to simulate unique index

Attachment: unique.mwb (application/octet-stream, text), 4.78 KiB.

Changed the FK/index mapping implementation so that indexes created for foreign keys are no longer required to be marked as "FOREIGN". They now are created with type "INDEX" and can be changed to UNIQUE or other types.

marked bug #50462 as duplicate

fix confirmed in repository.

An entry has been added to the 5.2.20 changelog:

The FK/index mapping implementation has been changed so that indexes created for foreign keys are no longer required to be marked as FOREIGN. They now are created with type INDEX and can be changed to UNIQUE or other types.

Although, the artificial FOREIGN index type has been removed, MySQL Workbench still automatically creates and maintains indexes for each foreign key by naming them after the keyname and keeping the names (FK to IDX) synchronized.