MySQL Bugs: #55399: not able to create foreign key relationships

Bug #55399	not able to create foreign key relationships
Submitted:	20 Jul 2010 13:31	Modified:	6 Jan 2011 9:37
Reporter:	lokesh subbareddy	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S1 (Critical)
Version:	5.2.25 CE	OS:	Windows (Ubuntu)
Assigned to:	Alfredo Kojima	CPU Architecture:	Any

Description:
Not able to create foreign key relationships between 2 tables .

How to repeat:
got to the workbench create new table .Try to map foreign ket relationship with other table .Then i am not able to select the column for the foreign key relationships

it is stopping me from creating tables

Please, upload problematic .mwb file and explain what foreign key(s) you want to add for them.

I have two tables employee , employee_resumes Which are having common column employee_id .Now i want to define foreign key relationship between employee_id of employee and employee_id of employee_resumes.But check boxes in the work bench are read only .PFA image file.

Are you 100% sure that columns have exactly the same data types? I still need .mwb file to check if this is a bug in Workbench.

Hi ,
         I haven't saved .mwb file.But i have clearly indicated the problem what i am facing.Even you can repeat the same steps and check.The data types of the both the columns are 'INT'. still i wasn't able to check the check box.

Sample model with foreign key defined as described

Attachment: bug55399.mwb (application/octet-stream, text), 5.90 KiB.

Check .mwb file I've just uploaded. It has the foreign key you wanted, and I had no problems defining it (on Mac OS X). So, there should be some details that you are missing in your bug description (or I am failing to understand from it).

screeshot of ste

Attachment: screenshot.zip (application/zip, text), 486.10 KiB.

Hi ,
         I am not using any model as such. I am directly trying to create SQL Editor.
I have attached screen shots of the steps what i have followed.

1.Once i open the work bench i will create new connection.
2.Then i will double click on the connection.It will open one SQL editor.Then i will create my own schema.
3. Then i started creating Tables one table is new_table and on other table is employee_resumes.
4.While Creating employee_resumes I tried defining foreign key relationship with new_table .But i am not getting any options.

I have provided the screen shots of the steps what i have followed.

Many thanks for writing a bug report.

Verified as described.

Test:

DROP SCHEMA IF EXISTS bug55399
CREATE SCHEMA bug55399;
USE bug55399;

CREATE TABLE t(i INTEGER, j INTEGER, PRIMARY KEY(i))ENGINE=INNODB;
CREATE TABLE t1(i INTEGER, j INTEGER, t1 INTEGER, PRIMARY KEY(i), FOREIGN KEY(t1) REFERENCES t(i))ENGINE=INNODB;

Open Workbench SQL Editor

Right click onto schema bug55399

Create new table

Try to create a foreign key for that talbe to one of the existing tables.

let me know is there any temporary hack for this bug?

Hey,
I recently had problems with this bug myself, but after reading the comments it appeard that my colom types weren't matching, colom1 was a INT and colom2 was a INT(11).
after adjusting this the relation worked again, might help you guys.

the problem is, that creating a column using the datatype 'INT' or 'INTEGER' will result by default in a column created as 'INT(11)' - which is not identified being the same as INT or INTEGER.

So the 'workaround' in that case is to create your columns in editor explicitly as 'INT(11)' and workbench will let you hook them up.

we need to add feedback for this situation so user knows where the problem is located.

fixed

fix confirmed in repository

An entry has been added to the 5.2.31 changelog:

In the SQL Editor it was not possible to create a foreign key relationship between two tables.