Description:
(not sure about the category!)
I am aware that RESTRCIT adn NO ACTION are synonyms in MySQL In Foreign Key definitios as described at https://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.
"
.. but they are handled inconsistently.
How to repeat:
Let us create tables:
CREATE TABLE `parent` (
`id` int(11) NOT NULL,
`txt` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `child` (
`id` int(11) NOT NULL,
`link` int(11) DEFAULT NULL,
`txt` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
After executing "ALTER TABLE `fktest`.`child` ADD CONSTRAINT `dummy` FOREIGN KEY (`link`) REFERENCES `fktest`.`parent`(`id`) ON UPDATE RESTRICT ON DELETE RESTRICT; "
. ."SHOW CREATE TABLE `child`;" returns
CREATE TABLE `child` (
`id` int(11) NOT NULL,
`link` int(11) DEFAULT NULL,
`txt` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `dummy` (`link`),
CONSTRAINT `dummy` FOREIGN KEY (`link`) REFERENCES `parent` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
In other words the server simply ignores RESTRICT.
This contradicts the behavior with "NO ACTION". If I create teh same tables and next execute "ALTER TABLE `fktest`.`child` ADD CONSTRAINT `dummy` FOREIGN KEY (`link`) REFERENCES `fktest`.`parent`(`id`) ON UPDATE RESTRICT ON DELETE RESTRICT; " SHOW CREATE TABLE will return
CREATE TABLE `child` (
`id` int(11) NOT NULL,
`link` int(11) DEFAULT NULL,
`txt` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `dummy` (`link`),
CONSTRAINT `dummy` FOREIGN KEY (`link`) REFERENCES `parent` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8
(and same with "CASCADE" and "SET NULL")
However in I_S.REFERENTIAL_CONSTRAINTS, "RESTRICT does appear in UPDATE_RULE adn DELETE_RULE columns
Suggested fix:
Return the RESTRICT (or its synonym NO ACTION) keyword in SHOW CREATE TABLE. And ensure consistency between SHOW and I_S.
