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

Cannot create UNIQUE index USING HASH algorithm in child columns

    XMLWordPrintable

Details

    Description

      Run the following statements, in which the index `i1` cannot be created.

      -- case 1
      		 
      CREATE TABLE t1 (c1 CHARACTER CHARACTER SET `BINARY` PRIMARY KEY);
      		 
      CREATE TABLE t2 (c1 BIT, FOREIGN KEY (c1) REFERENCES t1(c1));
      		 
      CREATE UNIQUE INDEX i1 USING HASH ON t2(c1); -- [HY000][1025] (conn=31) Error on rename of './test/#sql-alter-1-1f' to './test/t2' (errno: 150 "Foreign key constraint is incorrectly formed")

      However, when removing the UNIQUE in the CREATE INDEX statement, the index `i1` is created.

      -- case 2
      		 
      CREATE TABLE t1 (c1 CHARACTER CHARACTER SET `BINARY` PRIMARY KEY);
      		 
      CREATE TABLE t2 (c1 BIT, FOREIGN KEY (c1) REFERENCES t1(c1));
      		 
      CREATE INDEX i1 USING HASH ON t2(c1); -- Succeed

      It seems that the newly created index in case 1 cannot be used in FOREIGN KEY, while the index in case 2 is appliable.

      I think the UNIQUE constraint should not cause such difference. Moreover, according to the manual, the columns in the child table must be a BTREE, instead of HASH. It means that the index in case 2 should not be appliable.

      Attachments

        Issue Links

          duplicates

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33658 Can not add a foreign key (with a weird reason presented) when there is a UNIQUE multiple column index on the column with a long VARCHAR(N) as a suffix

          • Major - Major loss of function.
          • Confirmed

          Activity

            People

              nikitamalyavin Nikita Malyavin
              John Jove John Jove
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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