Bug #2423 ON DELETE RESTRICT doesn't show up in show create table

• Submitted: 16 Jan 2004 5:09
• Modified: 29 Jan 2004 13:30
• Reporter: Juan Pedro Reyes Molina
• Status: Not a Bug
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 4.0.17
• OS: Windows (Windows 2000 server)
• Assigned to: Heikki Tuuri
• CPU Architecture: Any

[16 Jan 2004 5:09] Juan Pedro Reyes Molina

Description:
If you create a table with on update cascade on delete restrict and issue a show create table on it only on update cascade show up in the output

How to repeat:
create this tables:

CREATE TABLE `maestro_de_bancos` (
  `Banco` varchar(4) NOT NULL default '',
  `Descripción` varchar(50) default NULL,
  PRIMARY KEY  (`Banco`)
) TYPE=InnoDB; 

CREATE TABLE `maestro_de_cuentas` (
  `Id cuenta` int(11) auto_increment,
  `Banco` varchar(4) default NULL,
  `Sucursal` varchar(4) default NULL,
  `Dígito Control` char(2) default NULL,
  `Cuenta corriente` varchar(15) default NULL,
  PRIMARY KEY  (`Id cuenta`),
  KEY `cuenta` (`Banco`,`Sucursal`,`Cuenta corriente`),
  CONSTRAINT FOREIGN KEY (`Banco`) REFERENCES `maestro_de_bancos` (`Banco`) ON UPDATE CASCADE on delete restrict
) TYPE=InnoDB; 

issue a show create table `maestro_de_cuentas` and you will see:

CREATE TABLE `maestro_de_cuentas` (
  `Id cuenta` int(11) NOT NULL auto_increment,
  `Banco` varchar(4) default NULL,
  `Sucursal` varchar(4) default NULL,
  `Dígito Control` char(2) default NULL,
  `Cuenta corriente` varchar(15) default NULL,
  PRIMARY KEY  (`Id cuenta`),
  KEY `cuenta` (`Banco`,`Sucursal`,`Cuenta corriente`),
  CONSTRAINT `0_60` FOREIGN KEY (`Banco`) REFERENCES `maestro_de_bancos` (`Banco`) ON UPDATE CASCADE
) TYPE=InnoDB;

[16 Jan 2004 11:21] Dean Ellis

Verified with 4.0.18/Linux.  Thank you.

[29 Jan 2004 13:30] Heikki Tuuri

Hi!

Since RESTRICT is the default action, it is not printed in the output of SHOW CREATE TABLE.

Regards,

Heikki