MySQL Bugs: #16039: Data integrity is not validated after foreign key constraints are enabled !

Bug #16039	Data integrity is not validated after foreign key constraints are enabled !
Submitted:	28 Dec 2005 11:05	Modified:	13 May 2010 16:04
Reporter:	g2293 g2293	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	5.0.19-BK	OS:	Linux (Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
When I set foreign key checks off (SET FOREIGN_KEY_CHECKS=0;) and insert wrong data into tables with foreign key references to others tables and then enable the foreign key constraints (SET FOREIGN_KEY_CHECKS=1;), Mysql does not throw any exceptions and holds on to the invalid data.  We are using InnoDb engine. 

How to repeat:

# Create Emp table 

CREATE TABLE `emp` (`emp_id` int(10) unsigned NOT NULL auto_increment,      `emp_name` varchar(50) NOT NULL, PRIMARY KEY  (`emp_id`));

# Create Dept table
CREATE TABLE `dept` (`dept_id` int(10) unsigned NOT NULL auto_increment,      `dept_name` varchar(50) NOT NULL, PRIMARY KEY  (`dept_id`));

# Create table dept_emp

CREATE TABLE `dept_emp` (`emp_id` int(10) unsigned NOT NULL,      `dept_id` int(10) NOT NULL, PRIMARY KEY  (`emp_id`,`dept_id`),
KEY `foreign-key-emp` (`emp_id`),                                     
KEY `foreign-key-dept` (`dept_id`),
CONSTRAINT `dept_emp_fk_1` FOREIGN KEY (`emp_id`) REFERENCES `emp` (`emp_id`) ON DELETE CASCADE,      
CONSTRAINT `dept_emp_fk_2` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`dept_id`) ON DELETE CASCADE
);

# Run queries
SET FOREIGN_KEY_CHECKS=0;INSERT INTO dept_emp(emp_id,dept_id) VALUES (3,1); SET foreign_key_checks = 1;

The above query does not throw any error even as the table dept_emp has data which violates the foreign key constraints as emp and dept tables have no rows.

Thank you for a bug report. 

Please, inform about the exact version of MySQL server used. I was not able to create your dept_emp table successfully with both foreign keys neither on 5.0.15-nt, nor on latest 5.0.19-BK on Linux:

mysql> ALTER TABLE `dept_emp`
    -> ADD FOREIGN KEY (`dept_id`) REFERENCES `dept` (`dept_id`) ON DELETE CASCADE;
ERROR 1005 (HY000): Can't create table './test/#sql-7afb_2.frm' (errno: 150)

But even with only one foreign key one can see the problem on 5.0.19-BK (ChangeSet@1.1995, 2005-12-26 13:40:07+04:00):

mysql> INSERT INTO dept_emp(emp_id,dept_id) VALUES (3,1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test/dept_emp`, CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY (`emp_id`) REFERENCES `emp` (`emp_id`) ON DELETE CASCADE)
mysql> set foreign_key_checks=0;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO dept_emp(emp_id,dept_id) VALUES (3,1);
Query OK, 1 row affected (0.00 sec)

mysql> set foreign_key_checks=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table dept_emp\G
*************************** 1. row ***************************
       Table: dept_emp
Create Table: CREATE TABLE `dept_emp` (
  `emp_id` int(10) unsigned NOT NULL,
  `dept_id` int(10) NOT NULL,
  PRIMARY KEY  (`emp_id`,`dept_id`),
  KEY `emp_id` (`emp_id`),
  KEY `dept_id` (`dept_id`),
  CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY (`emp_id`) REFERENCES `emp` (`emp_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

This statement: set foreign_key_checks=1; should give error message - there are rows in the table that violate constraint.

The MySql version I have is -  5.0.15-standard.

Changing the severity to 'Feature request'.

InnoDB should make a list of rows modified when the foreign key checks are lifted, and check afterwards if the constraints still hold. But this is a major new feature to implement.

Regards,

Heikki