Description:
Cannot create a foreign key constraint using the table editor as the SQL script generated is incorrect when selecting 2 columns from the foreign table.
How to repeat:
Create a table with an index column (test1.test1_ID).
Create a second table with an index column (test2.test2_ID).
Create a third table with two columns (test3.test1_ID, test3.test2_ID), both of which have foreign key constraints to the indexed columns in tables 1 and 2 (not part of this issue, but included for completeness)
Create a fourth table (test4) and using the table editor, create with a foreign key constraint for test3, and select columns test3.test1_ID and test3.test2_ID as the referenced columns.
The generated SQL for this will indicate that test3.test1_ID and test3.test1_ID are the referenced columns, not test3.test1_ID and test3.test2_ID as specifed:
ALTER TABLE `test`.`test4`
ADD INDEX `fk_test4_test3_idx` (`test4_test1_ID` ASC, `test4_test2_ID` ASC);
ALTER TABLE `test`.`test4`
ADD CONSTRAINT `fk_test4_test3`
FOREIGN KEY (`test4_test1_ID` , `test4_test2_ID`)
REFERENCES `test`.`test3` (`test3_test1_ID` , `test3_test1_ID`)
ON DELETE CASCADE
ON UPDATE CASCADE;
Workaround (after finally figuring out what was going on) is to manually change the generated SQL to reference the second column correctly.
Suggested fix:
Make sure the SQL generator parses the selected reference columns correctly.
