[MDEV-26051] Unexpected ER_FK_NO_INDEX_PARENT upon adding a self-referencing foreign key Created: 2021-06-30  Updated: 2023-04-27

Status: Open
Project: MariaDB Server
Component/s: Data Definition - Alter Table, Storage Engine - InnoDB
Affects Version/s: 10.2, 10.3, 10.4, 10.5, 10.6
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Minor
Reporter: Elena Stepanova Assignee: Thirunarayanan Balathandayuthapani
Resolution: Unresolved Votes: 0
Labels: upstream


 Description   

--source include/have_innodb.inc
 
create table t (pk int primary key, a int, key(a)) engine=InnoDB;
set foreign_key_checks= 0;
alter table t add foreign key (a) references t(a);
 
# Cleanup
drop table t;

ALTER above fails with

10.2 58252fff

ER_FK_NO_INDEX_PARENT (1822): Failed to add the foreign key constaint. Missing index for constraint '' in the referenced table 't'

It's not that surprising that it fails in general – after all, a self-referencing column hardly makes sense – but that it fails with ER_FK_NO_INDEX_PARENT although clearly there is an index.
With explicit ALGORITHM=COPY it succeeds (only producing a warning about an invalid FK in the error log).

Reproducible on 10.2-10.6, including older ones, as well as MySQL 5.7 and 8.0. I didn't check earlier versions.


Generated at Thu Feb 08 09:42:22 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.