Details
-
Type:
Bug
-
Status: Confirmed
-
Priority:
Minor
-
Resolution: Unresolved
-
Affects Version/s: 10.0.20
-
Fix Version/s: None
-
Component/s: Data Definition - Alter Table
-
Labels:
-
Environment: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`))
Gliffy Diagrams
Attachments
Issue Links
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
G'day Elena,
I see what you're saying, but to my mind "set foreign_key_checks=0" is supposed to prevent the database from checking the data of the foreign keys, not the schema.
For example:
{{
MariaDB [test]> set foreign_key_checks=0;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> create table user_table (email char(255) primary key);
Query OK, 0 rows affected (0.01 sec)
MariaDB [test]> create table prefs_table (id int(11) primary key, user int(11), foreign key (user) references user_table (email));
ERROR 1005 (HY000): Can't create table `test`.`prefs_table` (errno: 150 "Foreign key constraint is incorrectly formed")
}}
On the other hand, it is also true that this doesn't always happen (in either MySQL or MariaDB):
{{
MariaDB [test]> create table another_table (id int(11), other_id int(11), foreign key (other_id) references nonexistent_table (id));
Query OK, 0 rows affected (0.04 sec)
}}
I'd argue that that shouldn't be allowed either, or at the very least it should throw a warning.
Mind you, the MySQL manual says that "Setting foreign_key_checks to 0 also affects data definition statements: DROP SCHEMA drops a schema even if it contains tables that have foreign keys that are referred to by tables outside the schema, and DROP TABLE drops tables that have foreign keys that are referred to by other tables." so perhaps I'm just plain wrong :-/.
PS: Congratulations on your greatly improved error messages for foreign key problems!
I really don't see any problem at all here. Basically, what we have it:
So, if one insisted so much on creating the constraint upon table creation, why not to turn off foreign keys again upon inserting the data?
As a user, I would be rather unhappy if the server refused to create the table even after I specifically requested not to perform the foreign key check.
However, since upstream confirmed the bug report (for now), I will keep this one open as well, lets see what they come up with.