Foreign key allowed between InnoDB and MyISAM if "set foreign_key_checks=0"

Description

Also reporting in MySQL: https://bugs.mysql.com/bug.php?id=78255

Putting foreign keys between InnoDB and MyISAM should be disallowed at schema-change time (the create table should fail) because MyISAM doesn't support foreign keys. But it is allowed if foreign keys checks are switched off.

Inserting rows then fails with "ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails" even if values are valid

This may seem like a perverse set of circumstances, but it happens quite easily when using Django South migrations.

See also: http://nick.zoic.org/sql/mysql-foreign-keys-between-innodb-and-myisam/

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 MariaDB [test]> create table old_table ( id int(11) primary key ) engine=MyISAM; MariaDB [test]> create table new_table ( -> id int(11) primary key, -> old_table_id int(11), -> foreign key (old_table_id) references old_table (id) -> ) engine=InnoDB; ERROR 1005 (HY000): Can't create table `test`.`new_table` (errno: 150 "Foreign key constraint is incorrectly formed") MariaDB [test]> set foreign_key_checks=0; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> create table new_table ( id int(11) primary key, old_table_id int(11), foreign key (old_table_id) references old_table (id) ) engine=InnoDB; Query OK, 0 rows affected (0.02 sec) MariaDB [test]> set foreign_key_checks=1; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> insert into old_table values (1); Query OK, 1 row affected (0.00 sec) MariaDB [test]> insert into new_table values (1,1); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`new_table`, CONSTRAINT `new_table_ibfk_1` FOREIGN KEY (`old_table_id`) REFERENCES `old_table` (`id`))

Environment

linux debian jessie

Status

Assignee

Unassigned

Reporter

Nick Moore

Labels

External issue ID

None

External issue ID

None

Components

Affects versions

10.0.20

Priority

Minor