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

[28 Dec 2005 11:05] g2293 g2293
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.
[28 Dec 2005 13:36] Valeriy Kravchuk
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.
[29 Dec 2005 11:53] g2293 g2293
The MySql version I have is -  5.0.15-standard.
[29 Dec 2005 17:04] Heikki Tuuri
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

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.052 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.