Bug #35358 updating an incorrect foreign key to the same value does not raise error

• Submitted: 18 Mar 2008 5:28
• Modified: 19 Mar 2008 19:18
• Reporter: Rizwan Maredia
• Status: Verified
• Category: MySQL Server: Errors
• Severity: S4 (Feature request)
• Version: 5.1.23
• OS: Windows
• CPU Architecture: Any
• Tags: foreign_key_checks, UPDATE

[18 Mar 2008 5:28] Rizwan Maredia

Description:
Updating an incorrect foreign key(inserted by disabling foreign_key_checks) to the same value does not raise error after enabling foreign_key_checks. If the updated value is different and still violating FK constraint then an error is raised.

How to repeat:

Test: 
CREATE TABLE t1(a INT PRIMARY KEY)ENGINE = INNODB;
CREATE TABLE t2(a INT PRIMARY KEY,b INT)ENGINE = INNODB;
ALTER TABLE t2
ADD CONSTRAINT fk FOREIGN KEY (b) REFERENCES t1 (a);
# disable
SET @@session.foreign_key_checks = 0;
INSERT INTO t1 values (1),(2),(3);
INSERT INTO t2 values (10,1),(20,4);
# enable
SET @@session.foreign_key_checks = 1;
UPDATE t2 SET b=4 where a=20;

Output:
CREATE TABLE t1(a INT PRIMARY KEY)ENGINE = INNODB;
CREATE TABLE t2(a INT PRIMARY KEY,b INT)ENGINE = INNODB;
ALTER TABLE t2
ADD CONSTRAINT fk FOREIGN KEY (b) REFERENCES t1 (a);
SET @@session.foreign_key_checks = 0;
INSERT INTO t1 values (1),(2),(3);
INSERT INTO t2 values (10,1),(20,4);
SET @@session.foreign_key_checks = 1;
UPDATE t2 SET b=4 where a=20;

Suggested fix:
Updating a value to the same value should check for FK constraints. I think mysql optimizes such updates and does not updates any thing and thus no FK constraints apply here.

[19 Mar 2008 19:18] Susanne Ebrecht

Many thanks for writing a bug report.
In my eyes this is a bug and not a feature request.

Verified as described with 5.1-bk.