MySQL Bugs: #76227: FKs are checked in some cases, even if FOREIGN_KEY

Bug #76227	FKs are checked in some cases, even if FOREIGN_KEY_CHECK = 1
Submitted:	9 Mar 2015 13:28	Modified:	11 Mar 2015 16:33
Reporter:	Saverio Miroddi	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.6.23	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
It's defined in the manual that FOREIGN_KEY_CHECKS=0 causes FK constraints to be ignored (https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_foreign_key_ch...), with a reasonable assumption that this happens in every condition.

Under some conditions though, they are still checked - reported below.

How to repeat:
SET FOREIGN_KEY_CHECKS = 1;

# Fails, as expected
#
CREATE TABLE a( val, FOREIGN KEY testfk ( val ) REFERENCES b( val ) );

SET FOREIGN_KEY_CHECKS = 0;

# Doesn't fail, as expected
#
CREATE TABLE a( val INT, FOREIGN KEY testfk ( val ) REFERENCES b( val ) );

# Fails, unexpectedly
#
CREATE TABLE b( val INT );

# Doesn't fail
#
CREATE TABLE b( val INT, KEY( val ) );

This behavior is fully documented in our manual.

In the chapter 13.1.17.2, there is a section which describes the behavior when foreign_key_checks are set to 0. The text contains this description:

"
If you re-create a table that was dropped, it must have a definition that conforms to the foreign key constraints referencing it.

"
Hence, this is not a bug.

I see, It would be still helpful to specify  in the system variables chapter, that FOREIGN_KEY_CHECKS has some exceptions.

Your request is not a bad idea. However, I am not clear what chapter exactly do you have in mind.

Can you provide by its full number in the format of XX.YY.ZZ ???

I would add it to the section:

https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_foreign_key_ch...

with a simple note like 'Some exceptions apply, refer to chapter 13.1.17.2'.

The existing reference "See Section 14.6.6, “InnoDB and FOREIGN KEY Constraints” itself doesn't contain any mention of such exception.

We shall write to our team that is managing all our manuals to add the note that you specified.

Thanks.

The foreign_key_checks description has been revised as per your suggestion. Please allow 24 hours for the revised content to appear on-line.

Thank you for the report.