Cannot add foreign key constraint - Cannot find an index in the referenced table

I'm currently trying to add an association table and one of the foreign keys fails with this InnoDB status:

Cannot find an index in the referenced table where the referenced columns appear as the first columns or column types in the table and the referenced table do not match for constraint. Note that the internal storage type of ENUM and SET changed in tables created with >= InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables.

The table being references does have a primary key. It's in the same Charset/Collation. It is using InnoDB and the primary key has the same type as the FK in the new table.

It has an index named PRIMARY with the pk column checked and being #1.

This is the table being referenced

CREATE TABLE `oauthtokens` (
  `useridentifier` varchar(100) CHARACTER SET utf8 NOT NULL,
  `guid` varchar(36) CHARACTER SET utf8 DEFAULT NULL,
  `accesstoken` varchar(100) CHARACTER SET utf8 NOT NULL,
  `refreshtoken` varchar(100) CHARACTER SET utf8 NOT NULL,
  `expirytime` varchar(45) CHARACTER SET utf8 NOT NULL,
  `is_master_user` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`useridentifier`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

This is the new table I'm trying to create

CREATE TABLE `UserRoles` (
    `UserId` varchar(100) NOT NULL,
    `RoleId` int NOT NULL,
    PRIMARY KEY (`UserId`, `RoleId`),
    CONSTRAINT `FK_UserRoles_Roles_RoleId` FOREIGN KEY (`RoleId`) REFERENCES `Roles` (`Id`) ON DELETE CASCADE,
    CONSTRAINT `FK_UserRoles_oauthtokens_UserId` FOREIGN KEY (`UserId`) REFERENCES `oauthtokens` (`useridentifier`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

The FK_UserRoles_Roles_RoleId works but not the FK_UserRoles_oauthtokens_UserId one.

Solution

I tested this on 8.0.17 and got the error:

ERROR 3780 (HY000): Referencing column 'UserId' and referenced column 'useridentifier' in foreign key constraint 'FK_UserRoles_oauthtokens_UserId' are incompatible.

UserRoles.UserId is using the table's default character set of utf8mb4, but you're trying to make it reference a column oauthtokens.useridentifier which uses the character set utf8.

The foreign key column and the column it references must use the same character set and same collation.

I got it to work this way:

ALTER TABLE oauthtokens CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Then add the foreign key and it works.

You may like to read this more complete checklist of foreign key requirements here: https://stackoverflow.com/a/4673775/20860

I also wrote a chapter that explains possible foreign key errors in my book, SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming.