Stuck for errno: 150 "Foreign key constraint is incorrectly formed" in MariaDB

Question

I have this table:

CREATE TABLE `Attributes` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `Guid` uuid NOT NULL DEFAULT uuid(),
  `Slug` varchar(400) NOT NULL DEFAULT uuid(),
  `Locale` varchar(400) NOT NULL,
  `Order` bigint(20) NOT NULL DEFAULT 0,
  `Title` varchar(400) DEFAULT NULL,
  `ImageGuid` uuid DEFAULT NULL,
  `Filterable` bit(1) DEFAULT NULL,
  `CreatesVariant` bit(1) DEFAULT NULL,
  `AffectsValue` bit(1) DEFAULT NULL,
  `DisplayOnly` bit(1) GENERATED ALWAYS AS ((`Filterable` is null or `Filterable` = 0) and (`CreatesVariant` is null or `CreatesVariant` = 0) and (`AffectsValue` is null or `AffectsValue` = 0)) VIRTUAL,
  `DataTypeKey` varchar(400) NOT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `IX_Attributes_Unique_Guid` (`Guid`),
  UNIQUE KEY `IX_Attributes_Unique_Locale_And_Slug` (`Locale`,`Slug`) USING HASH,
  UNIQUE KEY `IX_Attributes_Unique_Locale_And_Title` (`Locale`,`Title`) USING HASH,
  CONSTRAINT `Ck_Attributes_NonEmptyGuid` CHECK (`Guid` <> '00000000-0000-0000-0000-000000000000'),
  CONSTRAINT `Ck_Attributes_NonEmptyImageGuid` CHECK (`ImageGuid` <> '00000000-0000-0000-0000-000000000000')
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

And then this table:

CREATE TABLE `EntityTypeHeteroAttributes` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `Module` varchar(400) NOT NULL,
  `EntityType` varchar(400) NOT NULL,
  `AttributeId` bigint(20) NOT NULL,
  `Filterable` bit(1) DEFAULT NULL,
  `CreatesVariant` bit(1) DEFAULT NULL,
  `AffectsValue` bit(1) DEFAULT NULL,
  `DisplayOnly` bit(1) GENERATED ALWAYS AS ((`Filterable` is null or `Filterable` = 0) and (`CreatesVariant` is null or `CreatesVariant` = 0) and (`AffectsValue` is null or `AffectsValue` = 0)) VIRTUAL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `IX_EntityTypeHeteroAttributes_Unique_AttributeId_And_EntityType` (`AttributeId`,`EntityType`,`Module`) USING HASH,
  CONSTRAINT `Ck_EntityTypeHeteroAttributes_NonEmptyEntityType` CHECK (`EntityType` <> ''),
  CONSTRAINT `Ck_EntityTypeHeteroAttributes_LowerCaseEntityType` CHECK (cast(`EntityType` as char charset binary) = lcase(`EntityType`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

When I want to create a foreign key, using this code:

alter table `EntityTypeHeteroAttributes`
add constraint FK_EntityTypeHeteroAttributes_AttributeId_Attributes_Id
foreign key (`AttributeId`)
references `Attributes` (Id)
on update cascade
on delete cascade

I get this error:

Can't create table Attributes.EntityTypeHeteroAttributes (errno: 150 "Foreign key constraint is incorrectly formed")

The same code works for hundreds of other tables in this database. I saw many questions, specifically this one which summarizes the problems.

Yet my case is not any of them. I have also created this fiddle.

I'm using:

MySQL version: 11.2.2-MariaDB-1:11.2.2+maria~ubu2204 through PHP extension MySQLi

Update: This table would be created with its foreign key without any problems:

CREATE TABLE `ModuleHeteroAttributes` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `Module` varchar(400) NOT NULL,
  `AttributeId` bigint(20) NOT NULL,
  `Filterable` bit(1) DEFAULT NULL,
  `CreatesVariant` bit(1) DEFAULT NULL,
  `AffectsValue` bit(1) DEFAULT NULL,
  `DisplayOnly` bit(1) GENERATED ALWAYS AS ((`Filterable` is null or `Filterable` = 0) and (`CreatesVariant` is null or `CreatesVariant` = 0) and (`AffectsValue` is null or `AffectsValue` = 0)) VIRTUAL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `IX_ModuleHeteroAttributes_Unique_AttributeId_And_Module` (`AttributeId`,`Module`),
  CONSTRAINT `FK_ModuleHeteroAttributes_AttributeId_Attributes_Id` FOREIGN KEY (`AttributeId`) REFERENCES `Attributes` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

Answer 1

150 is usually fixable by either

• Rearrange the order of the CREATE TABLEs so that the FK declaration has what it needs when it needs it.
• Move all the FK declarations after the CREATE TABLEs.

Meanwhile:

• Integer display width is deprecated; that is get rid of the (20) on BIGINTs.
• DEFAULT (uuid()) -- note extra parens.
• 400 is an unreasonable size for some of the columns, and it threatens to make some of the INDEXes too large.