Bug #91049 deleting duplicate complains about foreign key
Submitted: 28 May 2018 17:38 Modified: 6 Jun 2018 17:34
Reporter: Don Cohen Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.5.38 OS:Any
Assigned to: MySQL Verification Team CPU Architecture:Any

[28 May 2018 17:38] Don Cohen
I think this is a bug.  See if you agree.
MySQL [demo]> create table sup(id varchar(4), key(id)); 
Query OK, 0 rows affected (0.06 sec) 
MySQL [demo]> create table sub(id varchar(4), key(id),foreign key (id) references sup(id)); 
Query OK, 0 rows affected (0.07 sec) 
MySQL [demo]> insert into sup values ('A'); 
Query OK, 1 row affected (0.04 sec) 
MySQL [demo]> insert into sub values ('A'); 
Query OK, 1 row affected (0.05 sec) 
MySQL [demo]> insert into sup values ('A'); 
Query OK, 1 row affected (0.05 sec) 
MySQL [demo]> select * from sup; 
| id   | 
| A    | 
| A    | 
2 rows in set (0.00 sec) 
MySQL [demo]> delete from sup limit 1; 
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`demo`.`sub`, CONSTRAINT `sub_ibfk_1` FOREIGN KEY (`id`) REFERENCES `sup` (`id`)) 

I think it should be allowable to delete one of the rows as long as I leave one to satisfy the constraint.

How to repeat:
follow the script in description

Suggested fix:
The foreign key should not complain if there is still another row that satisfies it.
[6 Jun 2018 17:34] MySQL Verification Team

I disagree this is a bug. Standard is a bit sketchy here but this is how we implemented it. In most RDBMS you would not be able to create a reference to a column that's not unique (that could be considered a bug in mysql that it allow you to reference a non-unique column) so the way it's implemented now you have this behavior. 

Thanks for your report
[6 Jun 2018 17:48] MySQL Verification Team
Just a quick update, I did send this to my dev/standard colleagues for review as they might add something to my notes.

kind regards
[8 Jun 2018 14:02] MySQL Verification Team

Feature request created from this bug report