MySQL Bugs: #91049: deleting duplicate complains about foreign key

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:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.5.38	OS:	Any
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

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

Hi,

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
Bogdan

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
Bogdan

Update

Feature request created from this bug report
https://bugs.mysql.com/bug.php?id=91186

Thanks
Bogdan