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: | |
| 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
[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.