Description:
----[For better reports, please attach the log file after submitting. You can find it in /Users/nickradonic/Library/Application Support/MySQL/Workbench/log/wb.log]
I created compound primary keys. The referring table referenced them in the incorrect order. This made the index statement sort the fields incorrectly which broke the sql creation script -- it could not apply the foreign key constraints. Editing the 1,2 order of the foreign key references did NOT change the INDEX sql statement sort order.
How to repeat:
create two tables. in the first one create a two element string primary key. In the second table refer to the key items, but in the wrong order. See the sql statement for the referring table -- the index order is backwards. Go to the table - foreign key view, and change the element order. examine the sql creation statement again and see that the sql index sorting order is not revised..
Sample:
CREATE TABLE IF NOT EXISTS `human_encounter` (
`hash_uuid` VARCHAR(80) NOT NULL,
`version` VARCHAR(11) NOT NULL,
`person_id` INT NULL,
`human_person_hashuuid` VARCHAR(80) NOT NULL,
`human_person_version` VARCHAR(11) NOT NULL,
PRIMARY KEY (`hash_uuid`, `version`),
INDEX `human_person_fk_c_idx` (`human_person_hashuuid` ASC, `human_person_version` ASC),
CONSTRAINT `human_person_fk_c`
FOREIGN KEY (`human_person_hashuuid` , `human_person_version`)
REFERENCES `human_person` (`hash_uuid` , `version`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
The : FOREIGN KEY (`human_person_hashuuid` , `human_person_version`) line was reversed originally FOREIGN KEY ( `human_person_version`,`human_person_hashuuid`) -- until I deleted and re-added the hash and uuid columns back into the table; no amount of changing the ordering in workbench foreign key view changed the sql.
Suggested fix:
The index components for compound keys need to track the ordering selected in the foreign key view, and update as the ordering is changed. I suspect you have a stuck internal data structure - which tracks the elements, but doesn't update them with the foreign key view.
Description: ----[For better reports, please attach the log file after submitting. You can find it in /Users/nickradonic/Library/Application Support/MySQL/Workbench/log/wb.log] I created compound primary keys. The referring table referenced them in the incorrect order. This made the index statement sort the fields incorrectly which broke the sql creation script -- it could not apply the foreign key constraints. Editing the 1,2 order of the foreign key references did NOT change the INDEX sql statement sort order. How to repeat: create two tables. in the first one create a two element string primary key. In the second table refer to the key items, but in the wrong order. See the sql statement for the referring table -- the index order is backwards. Go to the table - foreign key view, and change the element order. examine the sql creation statement again and see that the sql index sorting order is not revised.. Sample: CREATE TABLE IF NOT EXISTS `human_encounter` ( `hash_uuid` VARCHAR(80) NOT NULL, `version` VARCHAR(11) NOT NULL, `person_id` INT NULL, `human_person_hashuuid` VARCHAR(80) NOT NULL, `human_person_version` VARCHAR(11) NOT NULL, PRIMARY KEY (`hash_uuid`, `version`), INDEX `human_person_fk_c_idx` (`human_person_hashuuid` ASC, `human_person_version` ASC), CONSTRAINT `human_person_fk_c` FOREIGN KEY (`human_person_hashuuid` , `human_person_version`) REFERENCES `human_person` (`hash_uuid` , `version`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE = InnoDB; The : FOREIGN KEY (`human_person_hashuuid` , `human_person_version`) line was reversed originally FOREIGN KEY ( `human_person_version`,`human_person_hashuuid`) -- until I deleted and re-added the hash and uuid columns back into the table; no amount of changing the ordering in workbench foreign key view changed the sql. Suggested fix: The index components for compound keys need to track the ordering selected in the foreign key view, and update as the ordering is changed. I suspect you have a stuck internal data structure - which tracks the elements, but doesn't update them with the foreign key view.