MySQL Bugs: #25056: FOREIGN_KEY

Bug #25056	FOREIGN_KEY_CHECKS
Submitted:	14 Dec 2006 0:31	Modified:	7 Feb 2007 16:39
Reporter:	[ name withheld ]	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S1 (Critical)
Version:	5.0.24a-community-nt/4.1/5.1	OS:	Windows (xp)
Assigned to:	MC Brown	CPU Architecture:	Any

Description:
setting FOREIGN_KEY_CHECKS {0,1} leave db in inconsistent state

How to repeat:
here is my script to reproduce:

C:\>type fkc.sql
drop database if exists test;
create database test;
use test;
create table t(a int primary key) type = innodb;
create table t2(a int primary key, foreign key (a) references t(a)) type = innod
b;
set foreign_key_checks=0;
insert into t2 values (1);
insert into t values (1);
set foreign_key_checks=1;
select * from t;
select * from t2;
set foreign_key_checks=0;
insert into t2 values (2);
set foreign_key_checks=1;
commit;
select * from t;
select * from t2;
#insert into t2 values (2);

and executing it results in no error even though 2 is not in t

C:\>mysql -uroot -pmysql <fkc.sql
a
1
a
1
a
1
a
1
2

Thank you for the bug report. FOREIGN_KEY_CHECKS = 1 does not cause the
server to scan all InnoDB tables verifying FK relationships. That option
is intended for special cases when the loading data is known is valid.
So I am verifying as documentation issue.

The documentation has been updated to note that enabling FOREIGN_KEY_CHECKS does scan existing table data.

I'm sorry for opening this again, but i'm not sure the comment by MC Brown is correct:

> [3 Jan 15:10] MC Brown 
> The documentation has been updated to note that enabling 
> FOREIGN_KEY_CHECKS does scan existing table data. 

The point was (as verified by MSolorzano):

> Miguel Solorzano 
>
> FOREIGN_KEY_CHECKS = 1 does _not_ cause the
> server to scan all InnoDB tables verifying FK relationships

ta,

jack

Thank you for the feedback.

I made a typo in my original comment; to clarify, I added the following paragraph to the manual:

Setting FOREIGN_KEY_CHECKS to 1 does not trigger a scan of the existing table data. Therefore,
rows added to the table while FOREIGN_KEY_CHECKS=0 will not be verified for consistency.

I believe this addresses the original point that FOREIGN_KEY_CHECKS is not a checking mechanism for rows already added to the DB.

Closing, as I've rechecked that the changes to the documentation describe the original concern.