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


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

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?



1 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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.