Description:
The "show create table" statement does not return an equivalent create-statement with constraints.
The original create statement contains:
CONSTRAINT `CS_FK_childs_ParentId` FOREIGN KEY `IX_FK_childs_ParentId`
what means, that the constraint is based on the index IX_FK_childs_ParentId
the returned statement of "show create table childs" contains:
CONSTRAINT `CS_FK_childs_ParentId` FOREIGN KEY (`ParentId`) REFERENCES `parents` (`ParentId`) ON DELETE NO ACTION ON UPDATE NO ACTION
that means that a constraint is created for the column ParentId what is correct, but it does not define the index to be used
in the case described in how to repeat this is a problem cause there are two indexes on the childs.ParentId column:
KEY `IX_FK_childs_ParentId` (`ParentId`),
KEY `IX ParentId` (`ParentId`),
How to repeat:
CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci */;
CREATE TABLE `parents` (
`ParentId` int(10) unsigned NOT NULL auto_increment,
`ParentText` varchar(45) collate latin1_general_ci NOT NULL,
PRIMARY KEY (`ParentId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
CREATE TABLE `childs` (
`ChildId` int(10) unsigned NOT NULL auto_increment,
`ParentId` int(10) unsigned NOT NULL,
`ChildText` varchar(45) collate latin1_general_ci NOT NULL,
PRIMARY KEY USING BTREE (`ChildId`),
KEY `IX_FK_childs_ParentId` (`ParentId`),
KEY `IX ParentId` (`ParentId`),
CONSTRAINT `CS_FK_childs_ParentId` FOREIGN KEY `IX_FK_childs_ParentId` (`ParentId`) REFERENCES `parents` (`ParentId`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
show create table childs; /*returns the incorrect statement*/
Suggested fix:
show create table has to analyse which index is used for the constraint and should return an equivalent statement.
