Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Not a Bug
-
10.0.20
-
linux debian jessie
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/
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`))
|