Bug #27140 SHOW CREATE TABLE and mysqldump does not show ON UPDATE/DELETE constraints
Submitted: 14 Mar 2007 16:27 Modified: 15 Mar 2007 17:44
Reporter: Johan Ekbäck Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.0.38-BK, 5.0.37-community-nt OS:Linux (Linux, Windows XP SP2)
Assigned to: Heikki Tuuri CPU Architecture:Any
Tags: constraints, CREATE TABLE, mysqldump, on delete, ON UPDATE, show

[14 Mar 2007 16:27] Johan Ekbäck
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.
[14 Mar 2007 16:35] Johan Ekbäck
I forgot that MySQL formated some output in lowercase only from which I copied and pasted some code, therefore some references got lowercased, for example ReferringTable became referringtable. Please be adviced on that when running the code on linux or any other case-sensitive system.
[14 Mar 2007 17:57] Valeriy Kravchuk
Thank you for a bug report. Verified just as described, also - with latest 5.0.38-BK on Linux.
[15 Mar 2007 17:44] Heikki Tuuri
Hi!

ON DELETE RESTRICT and ON UPDATE RESTRIXT are both redundant because that is the default behavior even without those clauses. That is why InnoDB does not print out them to SHOW CREATE TABLE.

Regards,

Heikki