MySQL Bugs: #56024: unable to check foreign key reference in create table in Workbench

Bug #56024	unable to check foreign key reference in create table in Workbench
Submitted:	16 Aug 2010 18:07	Modified:	25 Nov 2010 17:39
Reporter:	James Rome	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S3 (Non-critical)
Version:	5.2.25CE, 5.2.26, 5.2.28	OS:	Windows (Win7 32-bit in Bootcamp)
Assigned to:		CPU Architecture:	Any
Tags:	foreign key, reference

Description:
I created a simple table in the employees database on the command line:
create table dept
(deptid int not null primary key,
 deptname varchar(30)
) engine=INNODB;

I want to create a second table:
create table emp
(empid  int not null,
 name   varchar(100),
 job    varchar(100),
 deptid int not null,
 primary key (empid),
 foreign key (deptid) references dept (deptid)
) engine=INNODB;

but using workbench.
I make the table, and fill in the columns. dept is an int, and non-null.
I then click the foreigh key tab and select dept id in the first coun, and employees.dept in the second.

But, Workbench will not let me check any of the fields in dept as the reference, nor can I type anything in any field in the right-hand box in the attached picture. But I do not see how to attach anything!

What am I doing wrong?

How to repeat:
see above

Suggested fix:
Please tell me

what I see

Attachment: mysqlForeignKey.png (image/x-png, text), 19.04 KiB.

Well, I entered the emp table by hand, and then altered it in Workbench. And what did I find? The foreign key had a different name that was NOT in my SQL statement. How is a user supposed to know that he must give a new name to the foreign key?

I tried it again for a third table, and this time I chose a new name for the foreign key. But I am still unable to check anything in the right-hand pane, so this is still a bug.

Please, check if the same problem happens with a newer version, 5.2.26, and inform about the results.

I had already upgraded and tried it. Same results.
And I really wanted to use the Workbench for a class I am teaching Saturday!

Had you tried to check deptid column that is a foreign key? Then you should be able to select column it refers to (it will substitute PK of dept table automagically I suppose).

Simple model with FK that I've created

Attachment: bug56024.mwb (application/octet-stream, text), 5.89 KiB.

The check boxes are unclickable!

I tried this on a second windows 64-bit computer and got exactly the same results. Am I doing something wrong?

This is really strange. Does it still happen to you with a recent version, 5.2.28?

It happened for my whole class too. It is simple enough that you should be able to recreate it easily. But I have no time to try it again. I last tried it on the very latest version, 5.2.26.
It is a pretty critical thing IMHO. 
I loaded 5.2.28. It still does it. I mad a new table emp2 with a primary key and an int deptid that is a foreign key to dept.deptid.
In the foreign key tab, I wrote in deptid, selected 'employees'.dept as the referenced table, and I am unable to check any of the boxes under column

Can you attach a screenshot of the Columns tab for the new table you're trying to create, which would contain the FK?

None of the boxes are clickable.

Attachment: table.PNG (image/png, text), 103.31 KiB.

The scheme is trivial. I have a table Dept with DeptName and DeptID (primary key).
I make a second table Emp with Name, empid(primary key), job, and deptid (the foreign key).
I go to the foreign key tab (see pic) and nome of the fields in the right pane are clickable.

the reason is, that the INT column is created in server as INT(11) and WB doesn't allow hooking up mismatching column-types. As workaround, you can change the column-definition to `INT(11)` explicitly - then you'll be able to connect the columns.
We need to improve comparison of columns so Workbench treats INT == INT(11)

How is a user supposed to know this! I just picked INT in both cases. One would think it would be the same (or it should show up as being different). 
I think this bug should be elevated in importance.

How is a user supposed to know this! I just picked INT in both cases. One would think it would be the same (or it should show up as being different). 
I think this bug should be elevated in importance.

This has been fixed recently.

fix confirmed in repository

An entry has been added to the 5.2.31 changelog:

In the SQL Editor, in the Foreign Key tab of the new-table dialog, it was not possible to select any of the foreign key checkboxes.

This appears to still be an issue in version 5.2.31a.  The funny thing is that I am allowed to select a column if the "Referenced Table" chosen is the same as the table that I am editing.  Now that just doesn't make any sense.  I verified my INT's were all of the same type as I re-created the database ensuring INT(11) and not INT(10).  I am running on Windows 7 Ultimate - 64bit.