Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-26051

Unexpected ER_FK_NO_INDEX_PARENT upon adding a self-referencing foreign key

    XMLWordPrintable

Details

    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.

      Attachments

        Activity

          People

            thiru Thirunarayanan Balathandayuthapani
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.