Description:
MyISAM engine does not support FK constraints, yet MySQL completely ignores to warn the user about the fact in CREATE TABLE or ALTER queries. Instead, it accepts the query and returns OK status without even any warning.
I know this is documented in for example https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html, quoting "For storage engines that do not support foreign keys (such as MyISAM), MySQL Server parses and ignores foreign key specifications.". But still this behavior is questionable as I don't see any reason why at least a warning is not set.
In fact, this may lead to silent data consistency problems. Let's imagine some table is left forgotten as MyISAM by mistake and at some point foreign constraints are implemented in the schema. As adding such constraint succeeds and returns no error nor warning, nobody is aware of the problem just introduced.
How to repeat:
mysql [localhost:8017] {msandbox} (test) > CREATE TABLE parent (
-> id INT NOT NULL,
-> PRIMARY KEY (id)
-> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.02 sec)
mysql [localhost:8017] {msandbox} (test) > CREATE TABLE child (
-> id INT,
-> parent_id INT,
-> INDEX par_ind (parent_id),
-> FOREIGN KEY (parent_id)
-> REFERENCES parent(id)
-> ON DELETE CASCADE
-> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.02 sec)
mysql [localhost:8017] {msandbox} (test) > show create table child\G
*************************** 1. row ***************************
Table: child
Create Table: CREATE TABLE `child` (
`id` int(11) DEFAULT NULL,
`parent_id` int(11) DEFAULT NULL,
KEY `par_ind` (`parent_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.02 sec)
mysql [localhost:8017] {msandbox} (test) > drop table child;
Query OK, 0 rows affected (0.02 sec)
mysql [localhost:8017] {msandbox} (test) > CREATE TABLE child ( id INT, parent_id INT, INDEX par_ind (parent_id)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.02 sec)
mysql [localhost:8017] {msandbox} (test) > ALTER TABLE child ADD FOREIGN KEY fk1(parent_id) REFERENCES parent(id) ON DELETE CASCADE;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql [localhost:8017] {msandbox} (test) > show create table child\G
*************************** 1. row ***************************
Table: child
Create Table: CREATE TABLE `child` (
`id` int(11) DEFAULT NULL,
`parent_id` int(11) DEFAULT NULL,
KEY `par_ind` (`parent_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=FIXED
1 row in set (0.00 sec)
Suggested fix:
In my opinion CREATE TABLE or ALTER that tries to add FK contraints to storage engines that do not support them, should fail with error. If not, at least a warning should be triggered.
