Bug #13408 Incorrect output of SHOW CREATE TABLE with multiple referential actions

• Submitted: 22 Sep 2005 18:00
• Modified: 22 Sep 2005 18:26
• Reporter: Felix Geerinckx (Basic Quality Contributor)
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.11
• OS: Linux (Linux)
• Assigned to: MySQL Verification Team
• CPU Architecture: Any

[22 Sep 2005 18:00] Felix Geerinckx

Description:
When specifying different referential actions on one FK, e.g.

... ON UPDATE CASCADE ON DELETE RESTRICT

the output of SHOW CREATE TABLE is incorrect, but the referential actions are enforced.

How to repeat:
Run the following:
USE test;
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS atable, btable;

SET FOREIGN_KEY_CHECKS=1;
CREATE TABLE atable (
	id INT NOT NULL PRIMARY KEY
) engine=InnoDB;

DROP TABLE IF EXISTS btable;
CREATE TABLE btable (
	id INT NOT NULL PRIMARY KEY,
	aid INT NOT NULL,
	KEY (aid),
	CONSTRAINT xyz FOREIGN KEY (aid) REFERENCES atable (id) ON UPDATE CASCADE ON DELETE RESTRICT
) Engine=INNODB;

SHOW CREATE TABLE btable \G

INSERT INTO atable VALUES (1), (100);
INSERT INTO btable VALUES (1, 1), (2, 1);

# This should change the aid FK in btable from 1 to 2 => OK
UPDATE atable SET id = 2 WHERE id = 1;
SELECT * FROM btable;

# This should not be allowed => OK
DELETE FROM atable WHERE id=2;

# This should be allowed => OK
DELETE FROM atable WHERE id=100;

Suggested fix:
Fix output of SHOW CREATE TABLE

[22 Sep 2005 18:26] MySQL Verification Team

From bug: http://bugs.mysql.com/bug.php?id=13341

And quoting Heikki answer:

--------------------------------------------------------------------------
Hi!

SHOW CREATE TABLE does NOT omit ON DELETE CASCADE or ON UPDATE CASCADE.

It does omit ON DELETE RESTRICT and ON UPDATE RESTRICT, because RESTRICT is the
default behavior of FOREIGN KEY constraints. It does not add anything to write
those clauses to SHOW CREATE TABLE.

I believe this is not a bug. Changing the status to 'Not a bug'.

Regards,

Heikki
------------------------------------------------------------------------