MYSQL Cant没有创建表errno: 150外键约束的格式不正确

Question

使用mysql工作台创建表时，外键中出现了问题。

当我使用mysql工作台创建表时，工作台给出了以下错误：

服务器错误中执行SQL脚本的：错误1005:无法创建表 (errno: 150“外键约束格式不正确”)

Executing SQL script in server
ERROR: Error 1005: Can't create table `kipit`.`unlike_game_library` (errno: 150 "Foreign key constraint is incorrectly formed")
SQL Code:
        CREATE TABLE IF NOT EXISTS `kipit`.`unlike_game_library`
        (
            `id`         BIGINT       NOT NULL AUTO_INCREMENT,
            `created_at` DATETIME     NOT NULL DEFAULT now(),
            `updated_at` DATETIME     NULL     DEFAULT now(),
            `user_name`  VARCHAR(100) NOT NULL,
            `game_slug`  VARCHAR(255) NOT NULL,
            PRIMARY KEY (`id`),
            UNIQUE INDEX `user_name_UNIQUE` (`user_name` ASC),
            CONSTRAINT `fk_rated_game_library_user`
                FOREIGN KEY (`user_name`)
                    REFERENCES `kipit`.`user` (`name`)
                    ON DELETE CASCADE
                    ON UPDATE CASCADE,
            CONSTRAINT `fk_rated_game_library_game1`
                FOREIGN KEY (`game_slug`)
                    REFERENCES `kipit`.`game` (`slug`)
                    ON DELETE NO ACTION
                    ON UPDATE NO ACTION
        )
            ENGINE = InnoDB
            DEFAULT CHARACTER SET = utf8mb4
            COLLATE = utf8mb4_unicode_ci

SQL script execution finished: statements: 11 succeeded, 1 failed

Fetching back view definitions in final form.
Nothing to fetch

以下是我的疑问：

-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS = @@UNIQUE_CHECKS, UNIQUE_CHECKS = 0;
SET @OLD_FOREIGN_KEY_CHECKS = @@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS = 0;
SET @OLD_SQL_MODE = @@SQL_MODE, SQL_MODE =
        'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema kipit
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `kipit`;

-- -----------------------------------------------------
-- Schema kipit
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `kipit` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `kipit`;

-- -----------------------------------------------------
-- Table `kipit`.`user`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kipit`.`user`;

CREATE TABLE IF NOT EXISTS `kipit`.`user`
(
    `id`         BIGINT(20)   NOT NULL AUTO_INCREMENT,
    `role`       VARCHAR(45)  NOT NULL,
    `name`       VARCHAR(100) NOT NULL,
    `email`      VARCHAR(100) NOT NULL,
    `picture`    VARCHAR(100) NOT NULL,
    `message`    VARCHAR(255) NULL     DEFAULT NULL,
    `created_at` DATETIME     NOT NULL DEFAULT now(),
    `updated_at` DATETIME     NULL     DEFAULT now(),
    PRIMARY KEY (`id`),
    UNIQUE INDEX `name_UNIQUE` (`name` ASC)
)
    ENGINE = InnoDB
    AUTO_INCREMENT = 34
    DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `kipit`.`game`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kipit`.`game`;

CREATE TABLE IF NOT EXISTS `kipit`.`game`
(
    `id`        BIGINT       NOT NULL,
    `name`      VARCHAR(255) NOT NULL,
    `slug`      VARCHAR(255) NOT NULL,
    `image`     VARCHAR(255) NOT NULL,
    `genre`     VARCHAR(100) NULL,
    `publisher` VARCHAR(255) NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `slug_UNIQUE` (`slug` ASC),
    UNIQUE INDEX `name_UNIQUE` (`name` ASC),
    UNIQUE INDEX `id_UNIQUE` (`id` ASC)
)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `kipit`.`unlike_game_library`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kipit`.`unlike_game_library`;

CREATE TABLE IF NOT EXISTS `kipit`.`unlike_game_library`
(
    `id`         BIGINT       NOT NULL AUTO_INCREMENT,
    `created_at` DATETIME     NOT NULL DEFAULT now(),
    `updated_at` DATETIME     NULL     DEFAULT now(),
    `user_name`  VARCHAR(100) NOT NULL,
    `game_slug`  VARCHAR(255) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `user_name_UNIQUE` (`user_name` ASC),
    UNIQUE INDEX `game_slug_UNIQUE` (`game_slug` ASC),
    CONSTRAINT `fk_rated_game_library_user`
        FOREIGN KEY (`user_name`)
            REFERENCES `kipit`.`user` (`name`)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT `fk_rated_game_library_game1`
        FOREIGN KEY (`game_slug`)
            REFERENCES `kipit`.`game` (`slug`)
            ON DELETE NO ACTION
            ON UPDATE NO ACTION
)
    ENGINE = InnoDB
    DEFAULT CHARACTER SET = utf8mb4
    COLLATE = utf8mb4_unicode_ci;

SET SQL_MODE = @OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS = @OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS = @OLD_UNIQUE_CHECKS;

我检查相同的类型、unicode设置和唯一键，但它不工作

以下是我的数据库篡改结果(完整查询)：

https://www.db-fiddle.com/f/t2x2zvcVZZqxjsBsM7UXMF/3

+ INNO状态

2020-04-28 11:43:42 0x1d68 Error in foreign key constraint of table `kipit`.`if`:

        FOREIGN KEY (`user_name`)
            REFERENCES `kipit`.`user` (`name`)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT `fk_rated_game_library_game1`
        FOREIGN KEY (`game_slug`)
            REFERENCES `kipit`.`game` (`slug`)
            ON DELETE NO ACTION
            ON UPDATE NO ACTION
)
    ENGINE = InnoDB
    DEFAULT CHARACTER SET = utf8mb4
    COLLATE = utf8mb4_unicode_ci:
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.
Please refer to https://mariadb.com/kb/en/library/foreign-keys/ for correct foreign key definition.
Create  table `kipit`.`if` with foreign key constraint failed. Field type or character set for column 'user_name' does not mach referenced column 'name' near '
        FOREIGN KEY (`user_name`)
            REFERENCES `kipit`.`user` (`name`)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT `fk_rated_game_library_game1`
        FOREIGN KEY (`game_slug`)
            REFERENCES `kipit`.`game` (`slug`)
            ON DELETE NO ACTION
            ON UPDATE NO ACTION
)
    ENGINE = InnoDB
    DEFAULT CHARACTER SET = utf8mb4
    COLLATE = utf8mb4_unicode_ci'.

Answer 1

似乎“默认查集”和“排序规则”不匹配。让所有正在使用的表相同如何？

DROP TABLE IF EXISTS `kipit`.`user`;
CREATE TABLE IF NOT EXISTS `kipit`.`user`
(
    `id`         BIGINT(20)   NOT NULL AUTO_INCREMENT,
    `role`       VARCHAR(45)  NOT NULL,
    `name`       VARCHAR(100) NOT NULL,
    `email`      VARCHAR(100) NOT NULL,
    `picture`    VARCHAR(100) NOT NULL,
    `message`    VARCHAR(255) NULL     DEFAULT NULL,
    `created_at` DATETIME     NOT NULL DEFAULT now(),
    `updated_at` DATETIME     NULL     DEFAULT now(),
    PRIMARY KEY (`id`),
    UNIQUE INDEX `name_UNIQUE` (`name` ASC) 
)
    ENGINE = InnoDB
    AUTO_INCREMENT = 34
    DEFAULT CHARACTER SET = utf8mb4
    COLLATE = utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `kipit`.`game`;
CREATE TABLE IF NOT EXISTS `kipit`.`game`
(
    `id`        BIGINT       NOT NULL,
    `name`      VARCHAR(255) NOT NULL,
    `slug`      VARCHAR(255) NOT NULL,
    `image`     VARCHAR(255) NOT NULL,
    `genre`     VARCHAR(100) NULL,
    `publisher` VARCHAR(255) NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `slug_UNIQUE` (`slug` ASC) ,
    UNIQUE INDEX `name_UNIQUE` (`name` ASC) ,
    UNIQUE INDEX `id_UNIQUE` (`id` ASC) 
)
    ENGINE = InnoDB
    DEFAULT CHARACTER SET = utf8mb4
    COLLATE = utf8mb4_unicode_ci;    

DROP TABLE IF EXISTS `kipit`.`unlike_game_library`;    
CREATE TABLE IF NOT EXISTS `kipit`.`unlike_game_library`
(
    `id`         BIGINT       NOT NULL AUTO_INCREMENT,
    `created_at` DATETIME     NOT NULL DEFAULT now(),
    `updated_at` DATETIME     NULL     DEFAULT now(),
    `user_name`  VARCHAR(100) NOT NULL,
    `game_slug`  VARCHAR(255) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `user_name_UNIQUE` (`user_name` ASC) ,
    UNIQUE INDEX `game_slug_UNIQUE` (`game_slug` ASC) ,
    CONSTRAINT `fk_rated_game_library_user`
        FOREIGN KEY (`user_name`)
            REFERENCES `kipit`.`user` (`name`)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT `fk_rated_game_library_game1`
        FOREIGN KEY (`game_slug`)
            REFERENCES `kipit`.`game` (`slug`)
            ON DELETE NO ACTION
            ON UPDATE NO ACTION
)
    ENGINE = InnoDB
    DEFAULT CHARACTER SET = utf8mb4
    COLLATE = utf8mb4_unicode_ci;