Bug #55296 Cannot create foreign key with MySQLWorkbench
Submitted: 15 Jul 2010 17:27 Modified: 24 Aug 2010 14:38
Reporter: Leonard Sitongia Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Workbench: SQL Editor Severity:S2 (Serious)
Version:5.2.25 OS:Mac OS X (10.6.4)
Assigned to: CPU Architecture:Any

[15 Jul 2010 17:27] Leonard Sitongia
Description:
I'm trying the WorkBench on my Mac for the first time.  I created a table with two columns.  I want to make each a foreign key to other tables.  When I create the foreign key name and select the referenced table, I then see in the next frame to the right that the column names are listed with select buttons and referenced column names.  Clicking on those buttons does nothing.  The referenced column names pulldown shows the right columns, but selecting one leaves the field blank.  When I apply, the SQL generated is missing the referenced column names.  Even when I put those names in the SQL and re-apply, it fails.

The table is innoDB.

How to repeat:
Download and install on Mac.
created a table with two columns.  
create the foreign key name and select the referenced table
referenced column names pulldown shows the right columns, but selecting one leaves the field blank
[15 Jul 2010 17:31] Valeriy Kravchuk
Please, upload .mwb that demonstrates the problem for you and more specific instructions on what foreign key to add.
[15 Jul 2010 17:41] Leonard Sitongia
Table A has b_id
Table B has id
Go into the Workbench, select A, use the Foreign Keys tab:
FK              Reference Table             FK details column    Referenced Column
b_fk                      B                                           [] b_id                     (blank)

I cannot select the [] checkbox and cannot set (blank) to anything.

The SQL generated is:

ALTER TABLE `brightpoint`.`A` 
  ADD CONSTRAINT `b_fk`
  FOREIGN KEY ()
  REFERENCES `brightpoint`.`B` ()
  ON DELETE CASCADE
  ON UPDATE CASCADE
, ADD INDEX `test` () ;
[15 Jul 2010 17:43] Leonard Sitongia
I don't have an mwb.  I haven't generated a model.  Can I do that from the database I have?  I'll check.
[15 Jul 2010 17:45] Leonard Sitongia
model

Attachment: brightpoint.mwb (application/octet-stream, text), 16.36 KiB.

[15 Jul 2010 17:49] Leonard Sitongia
I've uploaded the mwb.  Instead of the previous simple example I made, let me describe more precisely.  I'm trying to add a foreign key to leonard_track_brightpoints.  It's a join table just like track_brightpoints.  I'm trying to regenerate the foreign keys in the latter into leonard_ table, duplicating it, for a test I'm doing.  I've created track_brightpoints using the old MySQL Query Browser on the Mac.  That has been flakey, crashing a lot, so I'm trying out the Workbench.
[15 Jul 2010 17:50] Leonard Sitongia
The foreign key should be added to leonard_track_brightpoints and refer to brightpoints.id.
[15 Jul 2010 18:43] Leonard Sitongia
I found the answer.  I didn't have the same settings for the leonard_tracks_brightpoints.track_id and tracks.id.  The latter was unsigned and I didn't have the former also unsigned.  That quietly causes the Workbench to fail to work right.  I made them both unsigned, and now the select button in the Workbench on the works right and I can create the foreign key.
[15 Jul 2010 19:09] Leonard Sitongia
Here's another problem.

This works:

ALTER TABLE leonard_tracks_brightpoints ADD CONSTRAINT fk FOREIGN KEY ( track_id ) REFERENCES tracks ( id );

This fails:

ALTER TABLE leonard_tracks_brightpoints ADD CONSTRAINT fk_tracks_brightpoints_tracks FOREIGN KEY ( track_id ) REFERENCES tracks ( id );

It fails on the length of the name of the constraint.
[16 Jul 2010 10:33] Valeriy Kravchuk
sample model

Attachment: bug55296.mwb (application/octet-stream, text), 5.73 KiB.

[16 Jul 2010 10:34] Valeriy Kravchuk
I see no problem with foreign key with the name you had used. See .mwb file uploaded. What error message you get and on what stage?
[20 Jul 2010 16:29] Leonard Sitongia
I tried the SQL you sent, and get this error:

Error code 1005, SQL state HY000: Can't create table 'brightpoint.#sql-1da6_2' (errno: 121)

When I simply shorten the FK to fk_tracks then it works.
[21 Jul 2010 5:10] Valeriy Kravchuk
What version of server, x.y.z, are you working with?
[21 Jul 2010 15:32] Leonard Sitongia
5.1.32
[22 Jul 2010 6:42] Susanne Ebrecht
Hello Leonard,

I think I found the problem.

Error 121 means duplicate key on write or update.

When you create a foreign key then innodb adds the foreign key constraint and it also adds an index for it.

When you drop the foreign key then innodb just will drop the constraint and the index will stay.

Usually, on creating a foreign key innodb should notice that an index for the column already exist. But we have had a bug here in older MySQL versions.

5.1.32 is very old ... actual version is 5.1.48.

In actual version these bugs are fixed.

My guess what happened on your system is that there already were an index or FK with that name. Maybe from some earlier tries to create the FK.

Because it already exist innodb wasn't able to create a new constraint or index with same name.

Just look with "show create table" if table already has index or constraint with that name.
[22 Jul 2010 15:57] Leonard Sitongia
I delete the table between tests, so there's no index hanging around for it (AFAICT).  

I did the test again, and there's no index prior to running the ALTER to create the foreign key.  Here's what I'm doing: first I delete table2, then run

CREATE  TABLE IF NOT EXISTS `table2` (
  `idtable2` INT NOT NULL ,
  `table1_idtable1` INT NOT NULL ,
  PRIMARY KEY (`idtable2`))
ENGINE = InnoDB;

ALTER TABLE table2 ADD CONSTRAINT
fk_tracks_brightpoints_tracks FOREIGN KEY ( table1_idtable1 ) REFERENCES
table1 ( idtable1 )
[23 Jul 2010 14:43] Valeriy Kravchuk
What are the results of:

show create table table1\G

then? Do you have any data in that table already?
[23 Jul 2010 14:47] Leonard Sitongia
table1	CREATE TABLE `table1` (
  `idtable1` int(11) NOT NULL,
  PRIMARY KEY (`idtable1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

No, there's no data in it.
[23 Jul 2010 17:35] Valeriy Kravchuk
It works with current server version:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.50-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE `table1` (
    ->   `idtable1` int(11) NOT NULL,
    ->   PRIMARY KEY (`idtable1`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    -> ;
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE  TABLE IF NOT EXISTS `table2` (
    ->   `idtable2` INT NOT NULL ,
    ->   `table1_idtable1` INT NOT NULL ,
    ->   PRIMARY KEY (`idtable2`))
    -> ENGINE = InnoDB;
Query OK, 0 rows affected (0.05 sec)

mysql> ALTER TABLE table2 ADD CONSTRAINT
    -> fk_tracks_brightpoints_tracks FOREIGN KEY ( table1_idtable1 ) REFERENCES
    -> table1 ( idtable1 );
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

So, please, try to use mysql command line client to execute statements above if you are still working with 5.1.32, to check if the same error will happen, or just upgrade to 5.1.48. I feel we have a server problem here, not Workbench bug.
[10 Aug 2010 15:19] Leonard Sitongia
We've upgraded.  Go ahead and close this ticket.
[24 Aug 2010 14:38] Valeriy Kravchuk
This problem was not a result of any bug in Workbench.