MySQL Bugs: #71499: CANT SYNC WITH FOREIGN OPTION RESTRICT DELETE

Bug #71499	CANT SYNC WITH FOREIGN OPTION RESTRICT DELETE
Submitted:	28 Jan 2014 12:58	Modified:	12 Mar 2014 1:11
Reporter:	the cupu	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	6.0.9	OS:	Windows (WIN 7 SP1 64BIT)
Assigned to:		CPU Architecture:	Any

Description:
hello, please fix a bug that MYSQL WB CANT detect foreign key RESTRICT 

because  from wiki there is a different NO ACTION AND RESTRICT 

from wiki http://en.wikipedia.org/wiki/Foreign_key
NO ACTION and RESTRICT are very much alike. The main difference between NO ACTION and RESTRICT is that with NO ACTION the referential integrity check is done after trying to alter the table. RESTRICT does the check before trying to execute the UPDATE or DELETE statement. Both referential actions act the same if the referential integrity check fails: the UPDATE or DELETE statement will result in an error.

In other words, when an UPDATE or DELETE statement is executed on the referenced table using the referential action NO ACTION, the DBMS verifies at the end of the statement execution that none of the referential relationships are violated. This is different from RESTRICT, which assumes at the outset that the operation will violate the constraint. Using NO ACTION, the triggers or the semantics of the statement itself may yield an end state in which no foreign key relationships are violated by the time the constraint is finally checked, thus allowing the statement to complete successfully.

How to repeat:
1,make relationship table with one to many using innodb
2. set foreign update to cascade and delete to NO ACTION
3. make sync model
4. change foreign delete from NO ACTION TO RESTRICT 
5. make sync model and nothing happened
6. change backk again from foreign delete from RESTRICT to NO ACTION
7. make sync model and also nothing happened

Suggested fix:
i think the main problem cause by
MYSQL WB CANT DETECT DIFFERENT RESTRICT AND NO ACTION
but wiki say there is a different, see description

Software version: 5.6.11 - MySQL Community Server (GPL)

The Wiki references Standard SQL. MySQL does not conform with the standard in this respect.

MySQL documentation at http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html:

RESTRICT: Rejects the delete or update operation for the parent table. Specifying RESTRICT (or NO ACTION) is the same as omitting the ON DELETE or ON UPDATE clause. 

NO ACTION: A keyword from standard SQL. In MySQL, equivalent to RESTRICT. The MySQL Server rejects the delete or update operation for the parent table if there is a related foreign key value in the referenced table. Some database systems have deferred checks, and NO ACTION is a deferred check. In MySQL, foreign key constraints are checked immediately, so NO ACTION is the same as RESTRICT.

.. so it really does not matter very much if I understand. There is no difference between NO ACTION and RESTRICT in MySQL. So what you are reporting is close to cosmetical IMO (if I understand).

Peter
(not a MySQL/Oracle person)

how about sync with any source ?
i think mysql WB not only for MYSQL there is another source for SQLdatabase

and that another source have a different between restrict and no action

6.0.9 still same problem

Thank you for the bug report. As mentioned in the MySQL docs, there is no difference in behaviour between NO ACTION and RESTRICT in MySQL, so making the change would have no effect. However, we will fix the problem in WB to avoid confusion.

Also see
http://bugs.mysql.com/bug.php?id=71669

I am not convinced that the problem occurring in the client/Workbench is not in reality a server issue.

Peter
(not a MySQL/Oracle person)

Fixed as of the upcoming MySQL Workbench 6.1.3 release, and here's the changelog entry:

MySQL Workbench will now see the "NO ACTION" and "RESTRICT" referential action
options as two different options.

Thank you for the bug report.