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

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 tab

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.

StackExchange Database Administrators Q#326040, answer score: 9