dolt: Can't add multiple foreign keys in `CREATE TABLE`
When I try to execute a CREATE TABLE-query with multiple foreign keys, that reference the same table, it fails with: “1105 - a foreign key with the name 4k50m025 already exists”.
If I remove one of the foreign keys from the create query and run it as an alter table-query it works just fine.
This fails, but not always:
CREATE TABLE `formstatus` (
`StatusID` int(11) UNSIGNED NOT NULL AUTO_INCREMENT ,
PRIMARY KEY (`StatusID`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=latin1 COLLATE=latin1_swedish_ci
AUTO_INCREMENT=1
;
CREATE TABLE `formstatusflow` (
`FlowID` int(11) UNSIGNED NOT NULL AUTO_INCREMENT ,
`FromStatusID` int(11) UNSIGNED NOT NULL ,
`ToStatusID` int(11) UNSIGNED NOT NULL ,
PRIMARY KEY (`FlowID`),
FOREIGN KEY (`FromStatusID`) REFERENCES `formstatus` (`StatusID`) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (`ToStatusID`) REFERENCES `formstatus` (`StatusID`) ON DELETE CASCADE ON UPDATE CASCADE,
INDEX `Statusflow.ToStatusID` (`ToStatusID`) USING BTREE ,
INDEX `StatusFlow.FromStatusID` (`FromStatusID`) USING BTREE
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=latin1 COLLATE=latin1_swedish_ci
AUTO_INCREMENT=1
;
I tried to create a PoC, but in doing so I wasn’t able to reproduce the error.
I have the same query open twice in Navicat. One of them fails, the other works.
One thing is for sure: The query always fails, with the same message, when my java application executes it.
For now I’m bypassing this by manually setting the contraint name.
About this issue
- Original URL
- State: closed
- Created 3 years ago
- Comments: 20 (11 by maintainers)
Thank you for, yet another, fast bug-squashing.
Just validated that it is working on my end.