linux debian jessie

I really don't see any problem at all here. Basically, what we have it:

• one attempts to create the constraint;
• server says that it's a bad idea;
• one says "I know better, I want to do it anyway";
• server does it upon the explicit request;
• one attempts to insert the data – server says "I told you it was a bad idea".

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.

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!

It's not a bug and foreign_key_checks=0 disables metadata checks too.

This is important when making and restoring backups — it allows to restore tables in any order, removing the need to do it in the dependency order.

Also, sometimes mysqldump creates a temporary MyISAM table that is later dropped — this is used when dumping a schema with views, because, otherwise, it'd need to understand dependencies and dump in the specific order (first tables, then some views, then views that use previously dumped views, etc).

Although, to my knowledge, mysqldump never needs to create a temporary MyISAM table when resolving foreign key dependencies, some other tools might.

So, foreign_key_checks=0 intentionally disables all metadata checks.