Description:
I have 3 tables in my schema : item, category, and z_item_category as a joint table. See
below for schema.
In MySQL WB, for table z_item_category I created a joint primary key on both fields.
Then I wanted to created a fk on each field, pointing to their respective source, but
when I try to do so in the Foreign Keys tab, I can't choose the local field to use as an
index column. The checkboxes are there, they're not not grayed out, but yet they're not
responding to my click events.
No error message is shown.
How to repeat:
-- the schema belows works as is in MySQL, and the FK perform as expected.
-- -----------------------------------------------------
-- Table `item`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `item` (
`item_id` INT(10) UNSIGNED NOT NULL auto_increment ,
`item_name` VARCHAR(255) NOT NULL ,
`item_unit` ENUM('m','m2','m3','h','kg','d') NOT NULL ,
`item_goalprice` DECIMAL(12,2) UNSIGNED NOT NULL DEFAULT 0 ,
`item_goalprice_validuntil` DATE NULL DEFAULT NULL ,
`item_orderby` TINYINT(3) UNSIGNED NOT NULL ,
PRIMARY KEY (`item_id`) )
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `category`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `category` (
`category_id` INT(10) UNSIGNED NOT NULL auto_increment ,
`parent_id` INT(10) UNSIGNED NULL DEFAULT NULL ,
`category_name` VARCHAR(100) NOT NULL ,
`category_orderby` TINYINT(3) UNSIGNED NOT NULL ,
PRIMARY KEY (`category_id`) ,
KEY `category_fk1` (`parent_id` ASC) ,
CONSTRAINT `category_fk1`
FOREIGN KEY (`parent_id` )
REFERENCES `erp_category` (`category_id` )
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `z_item_category`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `z_item_category` (
`item_id` INT(10) NOT NULL ,
`category_id` INT(10) NOT NULL ,
PRIMARY KEY (`item_id`, `category_id`) ,
KEY `zic_fk1` (`item_id`) ,
KEY `zic_fk2` (`category_id`) ,
CONSTRAINT `zic_fk1`
FOREIGN KEY (`item_id`)
REFERENCES `erp_item` (`item_id`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `zic_fk2`
FOREIGN KEY (`category_id`)
REFERENCES `erp_category` (`category_id`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = MyISAM;
Description: I have 3 tables in my schema : item, category, and z_item_category as a joint table. See below for schema. In MySQL WB, for table z_item_category I created a joint primary key on both fields. Then I wanted to created a fk on each field, pointing to their respective source, but when I try to do so in the Foreign Keys tab, I can't choose the local field to use as an index column. The checkboxes are there, they're not not grayed out, but yet they're not responding to my click events. No error message is shown. How to repeat: -- the schema belows works as is in MySQL, and the FK perform as expected. -- ----------------------------------------------------- -- Table `item` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `item` ( `item_id` INT(10) UNSIGNED NOT NULL auto_increment , `item_name` VARCHAR(255) NOT NULL , `item_unit` ENUM('m','m2','m3','h','kg','d') NOT NULL , `item_goalprice` DECIMAL(12,2) UNSIGNED NOT NULL DEFAULT 0 , `item_goalprice_validuntil` DATE NULL DEFAULT NULL , `item_orderby` TINYINT(3) UNSIGNED NOT NULL , PRIMARY KEY (`item_id`) ) ENGINE = MyISAM DEFAULT CHARACTER SET = utf8; -- ----------------------------------------------------- -- Table `category` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `category` ( `category_id` INT(10) UNSIGNED NOT NULL auto_increment , `parent_id` INT(10) UNSIGNED NULL DEFAULT NULL , `category_name` VARCHAR(100) NOT NULL , `category_orderby` TINYINT(3) UNSIGNED NOT NULL , PRIMARY KEY (`category_id`) , KEY `category_fk1` (`parent_id` ASC) , CONSTRAINT `category_fk1` FOREIGN KEY (`parent_id` ) REFERENCES `erp_category` (`category_id` ) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE = MyISAM DEFAULT CHARACTER SET = utf8; -- ----------------------------------------------------- -- Table `z_item_category` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `z_item_category` ( `item_id` INT(10) NOT NULL , `category_id` INT(10) NOT NULL , PRIMARY KEY (`item_id`, `category_id`) , KEY `zic_fk1` (`item_id`) , KEY `zic_fk2` (`category_id`) , CONSTRAINT `zic_fk1` FOREIGN KEY (`item_id`) REFERENCES `erp_item` (`item_id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `zic_fk2` FOREIGN KEY (`category_id`) REFERENCES `erp_category` (`category_id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE = MyISAM;