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

[19 Sep 2008 17:50] Bojan Vukasovic
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`)
[11 Oct 2008 7:18] Valeriy Kravchuk
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?
[17 Oct 2008 0:13] Bojan Vukasovic
Problem in picture

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

[12 Nov 2008 0:00] Bugs System
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".
[12 Nov 2008 18:09] Bojan Vukasovic
comment posted up
[14 Nov 2008 19:36] Valeriy Kravchuk
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.
[15 Sep 2009 14:33] Mike Lischke
Fixed in 5.1 + 5.2
[27 Nov 2009 18:33] Johannes Taxacher
fixed in reporitory for 5.1 and 5.2. 
will be included in 5.1.19.
is included in 5.2.4
[30 Nov 2009 16:08] Tony Bedford
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.
[4 Aug 2012 0:09] Garen Parham
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.
[4 Aug 2012 0:11] Garen Parham
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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.044 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.