There is no index in the referenced table where the referenced columns appear as the first column

Question

I have two EXISTING mysql/mariadb tables such as:

CREATE TABLE `ice_product` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `long_description` text DEFAULT NULL,
  `long_summary_descr` text DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `quality` varchar(255) DEFAULT NULL,
  `release_date` date DEFAULT NULL,
  `update_date` date DEFAULT NULL,
  `short_description` text DEFAULT NULL,
  `short_summary_descr` text DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `category_id` bigint(20) DEFAULT NULL,
  `supplier_id` bigint(20) DEFAULT NULL,
  `updated` bit(1) NOT NULL,
  `product_family_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_ice_product_cat` (`category_id`),
  KEY `idx_ice_product_supp` (`supplier_id`),
  KEY `idx_ice_product_updated` (`updated`),
  CONSTRAINT `fk_icecat_product_cat_id` FOREIGN KEY (`category_id`) REFERENCES `ice_category` (`id`),
  CONSTRAINT `fk_icecat_product_supp_id` FOREIGN KEY (`supplier_id`) REFERENCES `ice_supplier` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

and

CREATE TABLE `ice_product_manuals` (
  `product_id` bigint(20) NOT NULL,
  `descr` varchar(255) DEFAULT NULL,
  `url` varchar(800) DEFAULT NULL,
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_manual` (`product_id`,`url`) USING HASH
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

I wish to define a foreign key constraint

ALTER TABLE ice_product_manuals ADD CONSTRAINT fk_icecat_manuals_product_id FOREIGN
KEY (product_id) REFERENCES ice_product(`id`);

but the statement fails with

ERROR 1005 (HY000): Can't create table `mydb`.`ice_product_manuals` (errno: 150 "Foreign key constraint is incorrectly formed")

and SHOW ENGINE INNODB STATUS shows the following

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2023-04-15 19:05:36 0x7f70e4065640 Error in foreign key constraint of table `mydb`.`ice_product_manuals`:
Alter  table `mydb`.`ice_product_manuals` with foreign key `fk_icecat_manuals_product_id` constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns.

• same engine
• same charset and collate
• both table have indexes
• both table are empty

where is the problem?

https://dbfiddle.uk/psQC_4jV

Answer 1

You need an index on the referencing table on ice_product_manuals (product_id).

See: dbfiddle-1

The composite index you have, cannot be used for the FK constraint for a peculiar reason. Normally, two (or more) column indexes can be used for FK constraints if the constraint is on the first column(s) of the index.

There is however feature MDEV-371 where MariaDB implemented UNIQUE indexes for very long columns (more than 3072 bytes), where it states:

Allow a user to create unique constraints of arbitrary length. This will be done on the upper layer, in the server, not in the engine. The server will create the invisible virtual column with a hash over the to-be-unique columns. And a normal BTREE index over this column. On insert or update it'll check the index for hash collisions and, if needed, will retrieve the actual rows to compare the data."

Your composite index is indeed on a very long (2nd) column (800 chars on utf8mb4), more than 3072 bytes, so it falls in this case.

So this BTREE index is on the hash of the two columns and it cannot be used for the FK checks.

See: dbfiddle-2 where your code works if the 2nd column is shorter - and doesn't need to create this btree-on-a-hash but a normal 2-column btree index.