MySQL Bugs: #62432: omit ON DEL/UPD RESTRICT in table' SQL, 'cause it's default MySQL action

Bug #62432	omit ON DEL/UPD RESTRICT in table' SQL, 'cause it's default MySQL action
Submitted:	14 Sep 2011 8:55	Modified:	20 Jan 2012 8:03
Reporter:	Max Turkin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S3 (Non-critical)
Version:	5.2.34, 5.2.35, 5.2.36	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	restrict, SQL

Description:
There are "ON DELETE RESTRICT" / "ON UPDATE RESTRICT" parameters in egnerated SQL. But it should not be so, because RESTRICT is default MySQL behavior.

If to execute "SHOW CREATE TABLE tablename" query in MySQL, RESTRICT does not shown, because it's default MySQL behavior (restricting of delete/update parent row). See bug #2423 http://bugs.mysql.com/bug.php?id=2423

Because of this difference I always have not matching tables in Database -> Synchronize Model.

How to repeat:
- Create table with foreign key
- in "foreign key options" dialog set "ON DELETE RESTRICT"
- save
- right click on the table in "physical schemata" or a diagram
- copy SQL to clipboard
- paste it somewhere and see that "ON DELETE RESTRICT" is there

Suggested fix:
When I set "ON DELETE RESTRICT" / "ON UPDATE RESTRICT" in "Foreign key options" dialog, it should not appear in create-sql for a table.

Or, do 2 separate "Skip in SQL generation" checkboxes for ON DELETE/ON UPDATE.

What server version, x.y.z, do you use? 

I do not see any not matching tables while working with 5.1.57 and 5.2.35 preview release on Windows.

Server version: 5.1.46-community MySQL Community Server (GPL).

Do you mean, next mysql version after 5.1.46 include "ON UPDATE/DELETE RESTRICT" into output of SHOW CREATE TABLE statement?

Please try version 5.2.35. Thanks.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Same behavior confirmed for 5.2.35 CE rev. 7915

P.S. for previous comment: used mysql server is 5.5.17 MySQL Community Server

Reproduced as described for the reporter.
Seems like WB do not writes the "On Delete" options.
Ubuntu 11.04x64
WB 5.2.36 rev 8542

Hello Max.
Would you mind checking with WB newest version (5.2.36)?
Please confirm that now WB behaves as expected.
Thanks.

Bug is still there.
I see in SQL code:
...
  CONSTRAINT `fk_user_account`
    FOREIGN KEY (`id_account` )
    REFERENCES `mydb`.`account` (`id_account` )
    !!!!! ON DELETE RESTRICT !!!!!
    ON UPDATE CASCADE,
...
But I think that "ON DELETE RESTRICT" should not be there, because RESTRICT is default MySQL behavior on delete/update.

Verified with 5.2.36 on Windows XP. This is the code Workbench generates:

-- -----------------------------------------------------
-- Table `mydb`.`table2`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`table2` (
  `idtable2` INT NOT NULL ,
  `table1_idtable1` INT NOT NULL ,
  PRIMARY KEY (`idtable2`, `table1_idtable1`) ,
  INDEX `fk_table2_table1` (`table1_idtable1` ASC) ,
  CONSTRAINT `fk_table2_table1`
    FOREIGN KEY (`table1_idtable1` )
    REFERENCES `mydb`.`table1` (`idtable1` )
    ON DELETE RESTRICT
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

and this is how table is presented by server (version 5.5.17):

mysql> show create table table2\G
*************************** 1. row ***************************
       Table: table2
Create Table: CREATE TABLE `table2` (
  `idtable2` int(11) NOT NULL,
  `table1_idtable1` int(11) NOT NULL,
  PRIMARY KEY (`idtable2`,`table1_idtable1`),
  KEY `fk_table2_table1` (`table1_idtable1`),
  CONSTRAINT `fk_table2_table1` FOREIGN KEY (`table1_idtable1`) REFERENCES `tabl
e1` (`idtable1`) ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Fixed as of 5.2.40:

Queries that explicitly set RESTRICT will now
synchronize properly with those that do not, because
RESTRICT is the default behavior in MySQL.