CODEWITHSUNDEEP
mysqlmysql-workbenchmysql-error-1064
Sercan Ozdemir
Sercan Ozdemir

Reputation: 4692

Mysql Workbench Can't Select Foreign Key

First of all I've examined a lot of questions and googled it a lot, none of them solved my problem..

I'm creating my tables by using Mysql Workbench 6.3. I've created them by using only gui not a single query. After that I've tried to create some foreign keys for int(11) columns but GUI is not allowing me to do so.

Here's my Show Create Table 'tableName'

CREATE TABLE `item` (
  `id` int(11) NOT NULL,
  `description` varchar(300) NOT NULL,
  `maker` varchar(200) NOT NULL,
  `model` varchar(200) NOT NULL,
  `condition` varchar(200) NOT NULL,
  `locationId` int(11) NOT NULL,
  `categoryId` int(11) NOT NULL,
  `userId` int(11) NOT NULL,
  `lendTermsId` int(11) NOT NULL,
  `itemOptionsId` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

And the other one ;

CREATE TABLE `location` (
  `id` int(11) NOT NULL,
  `type` varchar(200) NOT NULL,
  `coordinateLat` varchar(200) NOT NULL,
  `coordinateLong` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

I'm trying to create a foreign key between location.id and item.locationId. Workbench GUI is not allowing me to select checkboxes...

enter image description here

As you can see from this image I can't select checkbox or select combobox value..

EDIT: I can manage this with query, but there are a lot of tables in some projects, dont want to do it all with queries... Is there something that I'm missing in workbench ?

Thanks in advance.

Upvotes: 8

Views: 7891

Answers (3)

AI0867
AI0867

Reputation: 396

As user4115463 said, this is caused by a difference in DataType. Make sure that both keys have the exact same data type:

  • the Datatype field should contain the exact same string (INT and INT(10) are not the same)
  • the B (Is binary) checkbox should match
  • the UN (Unsigned) checkbox should match

When selecting a checkbox, MySQL Workbench attempts to populate the dropdown next to the checkbox with matching columns, and if it can't find any matching columns, it decides to silently ignore the click on the checkbox instead.

Upvotes: 6

user4115463
user4115463

Reputation:

It might be a little late to answer this question but, Recently, I experience this issue in Workbench GUI and it was caused by difference in DataType.

Making sure that both fields have the same DataType seems to resolve the issue.

Hope this helps.

Upvotes: 2

SaviNuclear
SaviNuclear

Reputation: 861

Try the following Alt statement 

 ALTER TABLE `emp`.`item` 
ADD INDEX `location_idx` (`categoryId` ASC);
ALTER TABLE `emp`.`item` 
ADD CONSTRAINT `location`
  FOREIGN KEY (`categoryId`)
  REFERENCES `emp`.`location` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

I have tested through GUI check this screenshot enter image description here

Upvotes: 0

Related Questions