MySQL Bugs: #55296: Cannot create foreign key with MySQLWorkbench

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:	MacOS (10.6.4)
Assigned to:		CPU Architecture:	Any

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

Please, upload .mwb that demonstrates the problem for you and more specific instructions on what foreign key to add.

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` () ;

I don't have an mwb.  I haven't generated a model.  Can I do that from the database I have?  I'll check.

model

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

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.

The foreign key should be added to leonard_track_brightpoints and refer to brightpoints.id.

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.

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.

sample model

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

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?

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.

What version of server, x.y.z, are you working with?

5.1.32

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.

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 )

What are the results of:

show create table table1\G

then? Do you have any data in that table already?

table1	CREATE TABLE `table1` (
  `idtable1` int(11) NOT NULL,
  PRIMARY KEY (`idtable1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

No, there's no data in it.

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.

We've upgraded.  Go ahead and close this ticket.

This problem was not a result of any bug in Workbench.