MySQL Bugs: #39546: Create Foreign Key On Primary Key On same table

Bug #39546	Create Foreign Key On Primary Key On same table
Submitted:	19 Sep 2008 17:50	Modified:	30 Nov 2009 16:08
Reporter:	Bojan Vukasovic	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	5.0.27	OS:	Any
Assigned to:	Mike Lischke	CPU Architecture:	Any
Tags:	CHECKED

Description:
I would like to have ability to create following SQL trough designer mode:

CREATE TABLE `stranicakutija` (
  `str_ime_pk` varchar(100) NOT NULL,
  `kut_ime_id` varchar(100) NOT NULL,
  `kut_ime_iznad` varchar(100) default NULL,
-------
  KEY `stranicakutija_FKIndex3` (`str_ime_pk`,`kut_ime_iznad`),
  CONSTRAINT `stranicakutija_ibfk_3` FOREIGN KEY (`str_ime_pk`, `kut_ime_iznad`) REFERENCES `stranicakutija` (`str_ime_pk`, `kut_ime_id`) 

---> I cannot add through visual designer FOREIGN KEY reference from str_ime_pk to str_ime_pk... only to some other columns in existing table.

How to repeat:
KEY `stranicakutija_FKIndex3` (`str_ime_pk`,`kut_ime_iznad`),
  CONSTRAINT `stranicakutija_ibfk_3` FOREIGN KEY (`str_ime_pk`, `kut_ime_iznad`) REFERENCES `stranicakutija` (`str_ime_pk`, `kut_ime_id`)

Thank you for a problem report. Please, send exact CREATE TABLE statement you want to get from Workbench. Then, please, check if MySQL server accepts is as valid.

What Workbench version do you use?

Problem in picture

Attachment: problem.jpg (image/jpeg, text), 66.29 KiB.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

comment posted up

Verified just as described. Table like this:

mysql> CREATE TABLE `stranicakutija` (
    ->   `str_ime_pk` varchar(100) NOT NULL,
    ->   `kut_ime_id` varchar(100) NOT NULL,
    ->   `kut_ime_iznad` varchar(100) default NULL,
    ->   KEY `stranicakutija_FKIndex3` (`str_ime_pk`,`kut_ime_iznad`),
    ->   primary key(str_ime_pk, kut_ime_id),
    ->   CONSTRAINT `stranicakutija_ibfk_3` FOREIGN KEY (`str_ime_pk`, `kut_ime_
iznad`)
    -> REFERENCES `stranicakutija` (`str_ime_pk`, `kut_ime_id`) )
    -> ;
Query OK, 0 rows affected (0.17 sec)

can be created in mysql command line client but NOT in Workbench.

Fixed in 5.1 + 5.2

fixed in reporitory for 5.1 and 5.2. 
will be included in 5.1.19.
is included in 5.2.4

Added an entry to 5.1.19 and 5.2.4 changelogs:

MySQL Workbench EER Diagram view did not allow the creation of a foreign key constraint on a primary key.

This is not working in the latest version, 5.2.41.  After creating a new EER diagram from 'Reverse Engineer', the FK doesn't show up in the designer.

I tried saving and re-loading my .mwb file, and upon startup saw this message:

The model file '<path/to/my/file.mwb>' contained a problem which was successfully recovered: Index <tablename>.PRIMARY has an invalid column reference. The reference was removed from the index.

To add to previous comment: I tried manually adding the FK reference to my PK in the designer, but saw now visual change showing the new FK.  So, I then tried saving the .mwb file, closing Workbench, and re-launching it with my .mwb file.