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

REFERENTIAL_CONSTRAINTS.UNIQUE_CONSTRAINT_NAME incorrectly reports a non-unique index

    XMLWordPrintable

Details

    Description

      Hi, MariaDB developer. Thanks for reading my report. I find a bug.

      MariaDB reports the non-unique index i0 as UNIQUE_CONSTRAINT_NAME for a foreign key referencing parent(c1), even though parent(c1) has a UNIQUE constraint uk0.

      MySQL reports uk0.

      Reproduction

      CREATE TABLE parent (c0 INT, c1 INT, PRIMARY KEY (c0));
      CREATE TABLE child (c0 INT, c1 INT, PRIMARY KEY (c0));
      INSERT INTO parent VALUES (1, 1), (2, 2);
      INSERT INTO child VALUES (1, 1);
       
      ALTER TABLE parent ADD INDEX i0 (c1);
      ALTER TABLE parent ADD UNIQUE KEY uk0 (c1);
       
      ALTER TABLE child
      ADD CONSTRAINT fk0 FOREIGN KEY (c1) REFERENCES parent (c1);
       
      SELECT CONSTRAINT_NAME, UNIQUE_CONSTRAINT_NAME
      FROM information_schema.REFERENTIAL_CONSTRAINTS
      WHERE CONSTRAINT_SCHEMA = DATABASE()
        AND CONSTRAINT_NAME = 'fk0';
      

      Actual behavior

      MariaDB returns:

      CONSTRAINT_NAME | UNIQUE_CONSTRAINT_NAME
      ----------------+-----------------------
      fk0             | i0
      

      However, i0 is only a non-unique index.

      SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
      FROM information_schema.TABLE_CONSTRAINTS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'parent';
      

      returns:

      PRIMARY | PRIMARY KEY
      uk0     | UNIQUE
      

      Expected behavior

      UNIQUE_CONSTRAINT_NAME should report:

      fk0 | uk0
      

      because uk0 is the UNIQUE constraint on parent(c1).

      Attachments

        Issue Links

          Activity

            People

              thiru Thirunarayanan Balathandayuthapani
              jinhui lai jinhui lai
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.