Description:
Neither SHOW CREATE TABLE or mysqldump shows ON UPDATE/DELETE constraints for foreign keys.
CREATE TABLE `referringtable` (
`PrimaryKey` tinyint(4) NOT NULL,
`ForeignKey` tinyint(4) NOT NULL,
PRIMARY KEY (`PrimaryKey`),
KEY `ForeignKey` (`ForeignKey`),
CONSTRAINT `referringtable_ibfk_1`
FOREIGN KEY (`ForeignKey`)
REFERENCES `referredtable` (`PrimaryKey`)
ON UPDATE RESTRICT
ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- becomes --
CREATE TABLE `referringtable` (
`PrimaryKey` tinyint(4) NOT NULL,
`ForeignKey` tinyint(4) NOT NULL,
PRIMARY KEY (`PrimaryKey`),
KEY `ForeignKey` (`ForeignKey`),
CONSTRAINT `referringtable_ibfk_1`
FOREIGN KEY (`ForeignKey`)
REFERENCES `referredtable` (`PrimaryKey`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Note that InnoDB still works as it should and must therefore have the constraints stored internaly somewhere, but since the constraints can not be retrieved, any export will be faulty.
SHOW TABLE STATUS LIKE doesn't list these constraints either so it cannot be used as a workaround either. Is there any other workaround for the time beeing?
I tested this code on 5.0.37-community-nt but it is probably not limited to just this version.
How to repeat:
-- Create a test-database and run the following code on it:
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
-- Table structure for table `ReferredTable`
DROP TABLE IF EXISTS `ReferredTable`;
CREATE TABLE `referredtable` (
`PrimaryKey` tinyint(4) NOT NULL,
`Data` text NOT NULL,
PRIMARY KEY (`PrimaryKey`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- Data for table `ReferredTable`
LOCK TABLES `ReferredTable` WRITE;
INSERT INTO `ReferredTable` VALUES (1,'DummyOne'),(2,'DummyTwo'),(3,'DummyThree');
UNLOCK TABLES;
-- Table structure for table `ReferringTable`
DROP TABLE IF EXISTS `ReferringTable`;
CREATE TABLE `referringtable` (
`PrimaryKey` tinyint(4) NOT NULL,
`ForeignKey` tinyint(4) NOT NULL,
PRIMARY KEY (`PrimaryKey`),
KEY `ForeignKey` (`ForeignKey`),
CONSTRAINT `referringtable_ibfk_1`
FOREIGN KEY (`ForeignKey`)
REFERENCES `referredtable` (`PrimaryKey`)
ON UPDATE RESTRICT
ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- Data for table `ReferringTable`
LOCK TABLES `ReferringTable` WRITE;
INSERT INTO `ReferringTable` VALUES (10,1),(20,2);
UNLOCK TABLES;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
-- try two illegal queries (due to constraints)
DELETE FROM ReferredTable WHERE `Data`='DummyTwo';
UPDATE ReferredTable SET PrimaryKey=22 WHERE `Data`='DummyTwo';
-- try two legal queries
UPDATE ReferredTable SET `Data`='OK to delete' WHERE `Data`='DummyThree';
DELETE FROM ReferredTable WHERE `Data`='OK to delete';
-- try to show the restrictions with create table
SHOW CREATE TABLE ReferringTable\G
-- try to show the restrictions with table status
SHOW TABLE STATUS LIKE 'ReferringTable'\G
-- show current version
SHOW VARIABLES LIKE "version";
Suggested fix:
Rewrite SHOW CREATE TABLE to include ON UPDATE/DELETE constraints in it's output.